Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal percents in pivot table
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal percents in pivot table
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 "tlc" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
% of subtotal in pivot table | Excel Discussion (Misc queries) | |||
% of Subtotal In Pivot Table | Excel Worksheet Functions | |||
pivot table percents and counts | Excel Discussion (Misc queries) | |||
% of subtotal in pivot Table | Excel Discussion (Misc queries) | |||
Subtotal vs Pivot table - or best way | Excel Discussion (Misc queries) |