Pivot table
 

How to calculate % on subtotal in pivot?

Pivot table
 

There's no option to calculate the percent of a subtotal in a pivot
table. You could do the calculation outside of the PivotTable.

Or, if you have two fields in the row area, and set the data field to
show % of Column, the result may be similar to this:

Region Product Sales (% of Col)
East Chair 20.00%
Table 25.00%
East Total 45.00%
West Chair 40.00%
Table 15.00%
West Total 55.00%
Grand Total 100.00%

Each product shows as a percent of the overall total, instead of a
percent of its region.

If you move Region to the Column area, the products will show as a
percent of the region's total:

Sales (%Col) Region
Product East West Grand Total
Chair 44.44% 72.73% 60.00%
Table 55.56% 27.27% 40.00%
Grand Total 100.00% 100.00% 100.00%


KD wrote:
How to calculate % on subtotal in pivot?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot table
 

Thanks, I moved my data to the Column area and that worked in terms of
showing the percentages the user requires; the only problem is it is not as
pretty to view since now a very wide pivot but since the % needs to be a part
of the pivot rather than an outside calculation it will have to do. Thanks
again for your help.

Cheers,
KD

"Debra Dalgleish" wrote:

There's no option to calculate the percent of a subtotal in a pivot
table. You could do the calculation outside of the PivotTable.

Or, if you have two fields in the row area, and set the data field to
show % of Column, the result may be similar to this:

Region Product Sales (% of Col)
East Chair 20.00%
Table 25.00%
East Total 45.00%
West Chair 40.00%
Table 15.00%
West Total 55.00%
Grand Total 100.00%

Each product shows as a percent of the overall total, instead of a
percent of its region.

If you move Region to the Column area, the products will show as a
percent of the region's total:

Sales (%Col) Region
Product East West Grand Total
Chair 44.44% 72.73% 60.00%
Table 55.56% 27.27% 40.00%
Grand Total 100.00% 100.00% 100.00%


KD wrote:
How to calculate % on subtotal in pivot?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html