Plotting grand total on pivot chart

[BBLui]

Hi,

Is there a way to plot the grand total from a pivot table on the pivot chart?

Thanks!

 

[xtremegrump]

QuickQ,

You could set the range to something like Sheet1!$A$1:$D$800, as current example is only to D7, and then sort out the blanks. Not the best, but this is what I currently do. I have not come up with a better solution. I have been trying to come up with something for over six months.


Kevin
Excel2007

 

[QuickQ]

Thank you Kevin. I am usig the range outside the pivot. It would just be great if I could include that grand total in the chart, so that it would update automatically.

 

[Osceana]

QuickQ,

You could set the range to something like Sheet1!$A$1:$D$800, as current example is only to D7, and then sort out the blanks. Not the best, but this is what I currently do. I have not come up with a better solution. I have been trying to come up with something for over six months.


Kevin
Excel2007

Hey, I found this thread while I was looking for a solution to my problem. I may have found a solution:

http://www.mrexcel.com/forum/showthread.php?t=603519

 

[jjoh0921]

I know this is an old, old, old thread but thought I would share the simple way that I performed this option. I am using Excel 2010 so no Power Pivot for me at this time. I think in theory its the same way as a calculated field BUT I couldn't get that to work. Basically, I take the original data set that is used to create the PIVOT TABLE and copy/paste it so each record is duplicated. Assuming I want to add across a row (i.e. Grand Total will be on the column names), change the field value in the column field of the duplicate rows to "Grand Total" (ex If your column names in the PIVOT TABLE were "Strawberry","Orange", and "Apple" -- any time the column field has those values, replace with "Grand Total" on the duplicate records). All the data is replicated but each one will have a new column field value of "Grand Total". Refresh your pivot table and make sure that "Grand Total" is an option as a column value in the PivotTable Field List. Now, you will have all your old columns and an additional column called "Grand Total" which can be used in a PivotChart. You may have to format the "Grand Total" Data Series to make it look the way you want on the PivotChart but at least you now have a Data Series in the PivotChart you can work with.

This method will have PROS and CONS.
Con = Doubles the size of the original data set
Con = Adding new records to your data set will require you to enter two lines of data (one line with the actual data and another one with the "Grand Total" as the value of the field used for columns) -- can be automated
Pro = You only need to make changes to your original data set. Refreshing the pivot table automatically makes changes to the "Grand Total" column.
Pro = Make pretty little PivotCharts with Grand Totals so your boss thinks that you are smart

 

[samerickson89]

I'm trying to do something similar. Basically my pivot chart is clustered by month, but in the axis label next to each month I'd like it to display the grand total for that month. Is that something I'd be able to do?