Pivot Tables - Calc % using Sub-Total, not Grand Total as base

[sandi]

Hi there - i have a pivot table, created using MS Excel 2000, that looks
like this:



Prod: Widgit



Area..Territory...200412..200501..200502

01.......01-01.......100.....100.....100

...........01-02.......100.....100.....100

01 Total..............200.....200.....200

02.......02-01.......300.....300.....300

...........02-02.......400.....400.....400

02 Total.............700.....700.....700

GRAND TTL.......900.....900.....900



what i would like to show is % CONTRIBUTION FOR EACH TERRITORY based on
their AREA, not the % of GRAND TTL.



What i am trying to produce would look like this:



Prod: Widgit



Area...Territory...200412..200501..200502

01....01-01.........50%......50%.....50%

.........01-02.........50%......50%.....50%

01 Total...........100%.....100%.....100%

02....02-01.........43%......43%......43%

.........02-02.........57%......57%......57%

02 Total...........100%.....100%.....100%



When I use the "% of column" function in the Field Settings, it calculates
the individual territory % contribution against the GRAND TOTAL, so i get
something like this:



Prod: Widgit



Area..Territory..200412..200501..200502

01.....01-01.......11%.....11%....11%

..........01-02.......11%.....11%....11%

01 Total............22%.....22%....22%

02....02-01........33%.....33%....33%

.........02-02........44%.....44%....44%

02 Total............78%.....78%....78%

GRAND TTL......100%....100%...100%



the only way i can figure to do this is to create separate pivot tables for
each different AREA...using AREA as the PAGE FIELD and selecting SHOW ALL
PAGES...then it works...



but what i would like is ONE table, on ONE page which shows % Contribution
of each TERR to the AREA...



i was hoping for a one-stop solution - without having to adjust my
database...



is this do-able ?? appreciate your assistance!

 

[tcowen]

Hi Sandi,
I had the same problem. I was able to get around it by turning off the
Auto-subtotal for the Region and creating it as a Calculated Item. Then
I created another Calculated Item to divide the item within the region
by the subtotal of the region. You also want to turn off the Column
grand total.
I left the data visible along with the calculations, that way I could
display/filter whatever they wanted to see.

Tim