Adding a calculated field in Pivot Table to use in Pivot Chart

[jaya_sinnathamby@yahoo.com]

I want to include the Grand Total Row in my charts. The data I am using is from a survey.


When I create the Pivot Table it shows the Grand Total, but the Grand Total will not appear in the Chart. From what I have read, I am supposed to use a calculated field, but I am just not able to do so.


What I am trying to do must be a very common requirement when using survey data so I am puzzled as to why I am not able to find a solution for this.

 

[Sajan]

Hello,

Welcome to the forum.


If you are looking to include the total for each series, you should be able to select "sum of ..." from the PivotTable Field List screen.


If you are looking for something else, please feel free to post some sample data showing the structure of your data. We might be able to help you based on that.


You can also post sample workbooks. Instructions are available in the green "sticky" notes in the main forum.


Cheers,

Sajan.

 

[Trader273]

Calculated columns in pivot tables can be helpful, but they are often too simple. I would take a look at that the "GetPivotData" function. Can be a little intimidating at first, but once you dive into it's quite easy and makes using pivot table & calculated columns much easier.

 

[jaya_sinnathamby@yahoo.com]

GetPivotData gets me the value of the Grand Total, which is good starting point.


How do I include that value within the Pivot Table in a calculated field so I can plot it in the Pivot Chart?

 

[jaya_sinnathamby@yahoo.com]

Sajan,


I have posted the workbook here:

https://docs.google.com/open?id=0BxXeZvAvqG3ja1FITkxFd2RoRkk


The first worksheet contains anonymized sample data.


The 2nd worksheet shows the Pivot table and chart, and you can see Overall (which is the Grand Total) does not appear in the chart.


I copied the Pivot table as a normal table(values) into the 3rd worksheet and created a chart, which shows Overall, but now the chart is no longer interactive (which is intrinsic to pivot chart). The user should be able to select the campuses.


If there is a way to combine GetPivotData and calculated field, there maybe a solution.


Hope there is a solution.