Percentages of Subtotals for Pivot Tables

[Craig]

I would like to generate percentages as percents of the subtotals within a
pivot table, but have not figured out how to do so. It seems crazy to me
that this would not be a built in feature.

 

[Debra Dalgleish]

Unfortunately, there's no built in calculation to show percent of
subtotal. Perhaps you could change the layout, and put one field in the
column area and one in the row area. Then, in the data area, and
calculate the data field as % of Column, as shown here:

http://www.contextures.com/xlPivot10.html

 

[Ted M H]

Problem:
I need to get my pivot table to return a percent of subtotal instead of a
percent of column.
Pivot Data (columns A:C with desired result in column D):

Region State Sales Percent of Sales
North Montana 10 10 %
Idaho 90 90 %
North Total 100 100 %
West Calif 50 25 %
Oregon 100 50 %
Arizona 50 25 %
West Total 200 100 %

I can easily do this using the Show Value As % of Column option with field
settings for the Sales Field, but as the description implies this gives me
just the percent of the grand total for the column rather than for the
subtotals.

Unfortunately, PivotTables can't do the calculation I need.

Workaround:
Put the following formula in cell D5 and then just copy down in Column D to
the last row of the pivot table in columns A:C.

=C5/INDEX(A5:INDIRECT("C"&COUNTA(C:C)+3),MATCH("*
total",A5:INDIRECT("A"&COUNTA(C:C)+3),0),3)

For this to work you should have the Pivot table arranged in Tabular form
and the first row item should be in row 5 (column headings, page fields, etc.
in rows 1 - 4).
Also, don't override the default Subtotal names since the formula looks for
the literals *total* to find the subtotal rows. And be sure to put the
formula in col D before you do any filtering in the pivot table. If you make
any big changes to the pivot table, just reenter the formula and copy it down
again.