Pivot Table SubTotals

[Igwiz]

Afternoon all. Quick Pivot Table question if I may.

I have a Sum of Amount (of say fruit) in a datafield that is % of total. This works fine when I have, say, just one person showing.

Joe Fruit 10 50%
Veg 10 50%
Total 20 100%

However if the pivot table is showing all people, the % of total shows the % of the grand total and not the per person total which makes it not helpful for summaries.

Joe Fruit 10 25% (but I want it to show 50%
Veg 10 25% ie 50% of Joe)
Joe Total 20 50%
Bob Fruit 10 25%
Veg 10 25%
Bob Total 20 50%
Grand Total 40 100%

I can't work out what option I need to tick in order for it to use subtotals like this. Any help would be appreciated.
Many thanks.
Ig

 

[dianemarie]

Hello, I assume your data is most likely more complicated than your example, but if you pivot the data so that it looks like this:

Fruit Vegetables
Joe
Bob
Jane

One solution is to choose %ofRow in the Options of the pivot wizard (double-click the data field in Layout that is being used for the calculations to get to the Options box). This will give you the % of Fruit & Vegetables against each individual's total of both.

 

[Igwiz]

Thanks for the tip but aware of that one already. You are right in saying the data I am using is more complicated. Following on the example, if you then break fruit into apples and bananas and veg into carrots and sprouts you run into the same problem on either columns or rows. Effectively what I am trying to do is summarise all page data onto a single sheet but I guess this just cannot be done. Thanks anyway.
IG