Plotting grand total on pivot chart

BBLui

New Member
Joined
Sep 13, 2010
Messages
1
Hi,

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

Thanks!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi, good answer :( But what if both row and total should be visible in pivot chart (e.g. region A, region B, region C AND all regions)? Probably not supported by Excel 2007/2010!? What I did as ugly workaround is copying/referencing the pivot table to a standard table and created chart on that including all rows. Would very much appreciate a proper solution directly with Pivot function only.
 
Upvote 0
hi

I don't understand the specifics of what is wanted. I expect a solution is to have the plot data set up as you want it in the pivot table; and it sounds like that would be achieved by having your individual data values for regions A, B & C and also a grand total. I'm thinking this could be added artificially - like region D but called 'grand total' - to achieve the desired chart. That new data can be added using SQL - again, specific to your data. If creating manually, via the external data option at the first step of the pivot table wizard (ALT-D-P). Start in a separate workbook from the data. The below SQL gives the idea.

HTH, Fazza

Code:
SELECT region, value
FROM YourData
UNION ALL
SELECT 'Grand Total' AS [region], SUM(value) AS [value]
FROM YourData
GROUP BY 'Grand Total'
 
Upvote 0
Hi Fazza, thanks for your reply. I guess you got my point: even when in pivot table I tick 'Show Grand Totals for row and/or column' the total is just visible in the pivot table but not added to the pivot chart. I have to admit I'm not good with SQL so probably need to play around some time with your SQL code. I had hoped that MS Excel might have a chart setting to include also here Grand Totals as in Pivot Table. Thanks once more!
 
Upvote 0
Re: Plotting grand total on pivot chart (Excel 2007)

AKra,

Did you get this to work? I have the same problem now.

Alt D + P does not seem to work for me in Excel 2007

I appreciate any further help!
 
Upvote 0
Hi QuickQ, yep - I did. Using the pivot 'calculated item' I created a formula to sum up; this new field is then included into the pivot graph. Br, AKra
 
Upvote 0
Here's the problem I have. I have a tag on each record in my raw data indicating whether the record corresponds to A or B. That tag is my column label. The value section is populated by a Quantity field. The first column on the left has names.

All together, I have one column with names, one with Quatities for A and one with Quantities for B. When I create a calculated field to add up SUM(Quantity), I get a total for A (duplicate of A), one total for B (duplicate of B) and another Grand Total which again does not show up on my chart.

AKra: how how did you solve this issue? What was your formula in the calculated field?

Thank you!
 
Upvote 0
Try this. H5 through K13 is the pivot table, chart populated with the following sum of qty A, sum of qty B and Grand Total.


Excel Workbook
ABCDEFGHIJK
1NameQTY AQTY BGrand Total
2Bill6075134
3Suzy7178149
4Jill5577132
5Will6185145Values
6Joe6996165Row LabelsSum of QTY ASum of QTY BSum of Grand Total
7Jane8394177Bill59.577760174.86379743134.4415575
8Jane82.8607469893.76605749176.6268045
9Jill55.3035467976.71231627132.0158631
10Joe69.2973464795.82710862165.1244551
11Suzy71.116891577.89899111149.0158826
12Will60.9936982484.50148106145.4951793
13Grand Total399.1499901503.569752902.7197421
14
Sheet1




Kevin
 
Upvote 0
Thank you for your reply. That is what I am currently doing but...

The issue is that if there is a new person, like John, or several new people (add Arnold, JFK, and Marilyn), then the chart does not update automatically. You have to manually reset the range of the chart. Being able to plot the grand total from within the table would update the chart as soon as the pivot is updated.

On my spreadsheet, I have more than one chart and each time the pivots are refreshed, more than one "person" will be added, so it is time-consuming to re-set the ranges.

I do, however, appreciate your posting.

Any further help wuold be appreciated. I'm starting to think that what I want to do just can't be done... frustrating.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top