Pivot Table Percent Question
 

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?

Pivot Table Percent Question
 

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" wrote in message
...
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?

Pivot Table Percent Question
 

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" wrote:

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" wrote in message
...
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?

Pivot Table Percent Question
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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?

Pivot Table Percent Question
 

thanks.

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

but the array formulas have it going.

"Ashish Mathur" wrote:

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