Pivot Table Percent Question

[mark]

We have a pivot table of this form:

Type SubType Amount
a a 3
b 7
a Total 10
b a 11
b b 15
b total 26

We need to get another column to percent the amount as a percentage of the
subtotal rows...

for instance, 3 is 30% of the 'a Total' subtotal, 7 is 70%, 11 is 42.3% of
26, 15 is 57.7% of 26

etc.

I can't seem to find the right setting to do that.
Help?

 

[Ashish Mathur]

Hi,

The quickest way to do this is the following:

1. While on any figure in the data area, right click and go to field
settings, click on Options (Excel 2003);
2. In the Show as, select % age of column

Now you will see all figures as % age of the column total. to see the % as
% of subtotal, filter out "b" from the type and you will see the figures as
a % of subtotal "a"

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[mark]

I see what you're saying, but apparenly I confused the issue by
oversimplifying with my a,b examples.

We can't filter out b, or it totally distorts the data. In the first
subtotal line, the 10 which is the denominator of the desired calc, includes
a = 3 and b = 7, for a total of 10...

the a in the first column is totally unrelated to the a in the second
column... I should have given a better example by using a different letter.

I found three ways to do this...

1) A supplemental pivot table to summarize by the concatenation of relevant
row fields, with getpivotdata formulas beside the main pivot This of course
becomes inflexible with calcs outside of the pivot, relating to the pivot

2) An extension of 1), with the GetPivotData calcs worked back into the data
grid.. GetPivotData on the concatenation of the relevant row fields in the
main pivot, against the supplemental pivot... this provides the denominator
(the subtotal). Then divide this into the amount, and sum the individual
amounts in the pivot.

and probably the best way:

3) Just insert a column in the original data set, and use array formulas to
calculate the denominators (the subtotals), and then calc the individual row
percentages against that denominator... finally put that field in the pivot
directly... no need for a supplemental pivot.

 

[Ashish Mathur]

Hi,

I would have suggested method 3 (of your mail), had my previous solution not
worked. Anyways, I am glad you figures it out

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[mark]

thanks.

I was fairly surprised a pivot table didn't handle that better.

but the array formulas have it going.