% of subtotal custom calculation in a pivot table

[Guest]

hello everybody,

Debra Dalgleish mentions in her pivot table recipes book that although there
is no % of subtotal custom calculation available in a pivot table, one can
arrange fields in the row and column areas and then use % of row or % of
column to show these percentages. Does anyone know how to do this? Could you
provide a short example?

Microsoft should think about including this option in future Excel releases,
don't you think?

 

[Guest]

Well, if you select the dividend as the data field..
right-click in the data area, select Options...
show data as:
% of pick your base field.

Does that work for you?

 

[Debra Dalgleish]

Yes, it would be nice to have this option in a pivot table. In the
meantime, 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%