Subtotal percents in pivot table

[tlc]

I have a pivot table based on a list of data that has the following columns:
Budget Category, Line Item, Budget, YTD Spent, Balance, and % Spent. Budget
Category and Line Item are my row items. Sums of Budget, YTD Spent, Balance,
and % Spent are my data items. The % Spent is correct for each line item but
then those are all subtotaled which gives an inaccurate %. Here is what I am
getting:
Budget Category Line Item Budget YTD Spent Balance % Spent
Personnel Jim $30,000 $2,000 $28,000
6.67%
Bob $25,000 $1,000 $24,000
4%
Sue $40,000 $4,000 $36,000
10%
Personnel Total $95,000 $7,000 $88,000
20.67%

In this example, the % Spent in the Personnel Total row should be 7.37%
(7,000 / 95,000), not 20.67% (6.67+4+10).
How can I get the pivot table to give me what I am looking for in the %
Spent subtotal and total fields?

 

[Ashish Mathur]

Hi,

Remove % spent from the pivot. Click on any cell in the pivot table and
create a calculated field formula, say % spending, the formula for which
will be YTD Spent/Budget

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com