Grand Total Help.....

[hello its dean]

This is a screen grab of the pivot table I am working on..

help.jpg

Column F is working out the percentage variance of columns C & D. The trouble I am having is that the grand total at the bottom is just adding up all the percentages rather than working out the percentage variance of C & D.

Does any body know a way round this?

[JosephP]

is column f a calculated field in the pivot table? (a picture is of no help here unfortunately)

[hello its dean]

is column f a calculated field in the pivot table? (a picture is of no help here unfortunately)

It is indeed.

[JosephP]

what's the formula? that should not happen with a calculated field-the subtotals and grand totals should use the same calculation

[hello its dean]

Its just ='C'/'D' then I changed the number format to display it as a percentage..

[JosephP]

I'd have to see the actual workbook-with all data censored

[hello its dean]

Give me a few minutes and I will get something knocked up.

[hello its dean]

Here you go matey....


test.xlsx

[JosephP]

that is not a calculated field-it's a calculated item ;-)

they're quite different things and a calculated item will always show the totals the way you are seeing them

[hello its dean]

I did try and do it as a calculated field but the only fields it gave me were 'Row', 'Column', 'Value' and 'Page 1'.
I usually set up pivot tables by clicking on the pivot table button but for this one I used the Alt+D + P method as I needed multiple data sources. Would this have something to do with it? Is there a way to select multiple data sources without using Alt+D + P?

[JosephP]

you need the wizard to create a pivot table from consolidation ranges (I have only ever used that feature to unpivot data-it's terrible to use for pivots). if you can move all the data into one source table it would be better

[hello its dean]

I will try that. Thanks for all the help