Pivot Table wants % of Sub Totals

[Hacktastic]

Hi

Heres the question,

I have 2 columns of data in a pivot table and i want the % of the subtotal

Fruits: Apples - 5
Oranges - 10
Peaches - 5
Total: 20
Vegetables: Broccoli - 10
Corn - 10
Total: 20

I want the % of the sub total hence

Fruits: Apples - 25%
Oranges - 50%
Peaches - 25%
Total: 100%
Vegetables: Broccoli - 50%
Corn - 50%
Total: 100%

Any way to do this?

 

[SkipVought]

Hi,

I don't know of a way to do that in a PT.

However, it can be done using SUMIF...
[tt]
TYPE ITEM Cnt Avg
Fruit Apples 5 0.25
Fruit Oranges 10 0.5
Fruit Peaches 5 0.25
Vegetables Broccoli 10 0.5
Vegetables Corn 10 0.5
[/tt]
and then use the DATA > SUBTOTAL feature. Format for percent.

Skip,

_[red][/red]

 

[pbrodsky]

'Options' in the pivot table wizard - pick % of total

 

[SkipVought]

That gives percent of TOTAL

OP want percent of SUBTOTALS

Skip,

_[red][/red]

 

[pbrodsky]

sry - read it too fast

 

[Hacktastic]

thanks for all ur help...still waiting for a reply.

and it should be in a pivot

 

[SkipVought]

"...it should be in a pivot"

WHY?

Skip,

_[red][/red]

 

[SkipVought]

Here is specifically how it can be done. It took me about 5 minutes to do it start to finish...
[tt]
1) Use MS Query to get each column and the SUM of the value. faq68-5829
2) Get each row's percentage using SUMIF (I advocate using Named Ranges)...
[
D2: =c2/SUMIF(FoodType,A2,FoodCount)
where
column A is FoodType
column B is FoodName
column C is FoodCount
3) Use Data > Subtotals... to subtotal on columns C & D
[/tt]
Might take you a tad longer.



Skip,

_[red][/red]