Pivot Table - Calculated Column

[Clayton_]

Good morning,

I'm using Excel 2010 / Windows 7 Professional.

I have a table that I would like to summarize with a pivot table. I'd like the pivot to have sections for the 3 different types below and then show the detail for the various descriptions and calculate a "net income". That is straightforward and simple to do.

What I cannot figure out (and it may not be possible) is how I could add a column to the pivot table to show each row as a percentage of the "total income". So using the data below each row would be divided by the total income of $140,000.

[TABLE="width: 290"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Description[/TD]
[TD] Amount[/TD]
[/TR]
[TR]
[TD]Income[/TD]
[TD]Salary[/TD]
[TD] 100,000.00[/TD]
[/TR]
[TR]
[TD]Income[/TD]
[TD]Bonus[/TD]
[TD] 35,000.00[/TD]
[/TR]
[TR]
[TD]Income[/TD]
[TD]Consulting[/TD]
[TD] 5,000.00[/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[TD]Mortgage[/TD]
[TD] (18,000.00)[/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[TD]General Living[/TD]
[TD] (30,000.00)[/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[TD]Taxes[/TD]
[TD] (32,000.00)[/TD]
[/TR]
[TR]
[TD]Savings[/TD]
[TD]401k[/TD]
[TD] (16,000.00)[/TD]
[/TR]
[TR]
[TD]Savings[/TD]
[TD]Roth[/TD]
[TD] (5,500.00)[/TD]
[/TR]
</tbody>[/TABLE]



This is what I am trying to achieve. The "Sum of Amount" column is easy, but the Percent of Income column is what I cannot figure out.



[TABLE="width: 422"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD="align: right"]Sum of Amount[/TD]
[TD="align: right"]Percent of Income[/TD]
[/TR]
[TR]
[TD]Income[/TD]
[TD="align: right"]140,000[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]

Bonus​

[/TD]
[TD="align: right"]35,000[/TD]
[TD="align: right"]25.00%[/TD]
[/TR]
[TR]
[TD]

Consulting​

[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]3.57%[/TD]
[/TR]
[TR]
[TD]

Salary​

[/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]71.43%[/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[TD="align: right"]-80,000[/TD]
[TD="align: right"]-57.14%[/TD]
[/TR]
[TR]
[TD]

General Living​

[/TD]
[TD="align: right"]-30,000[/TD]
[TD="align: right"]-21.43%[/TD]
[/TR]
[TR]
[TD]

Mortgage​

[/TD]
[TD="align: right"]-18,000[/TD]
[TD="align: right"]-12.86%[/TD]
[/TR]
[TR]
[TD]

Taxes​

[/TD]
[TD="align: right"]-32,000[/TD]
[TD="align: right"]-22.86%[/TD]
[/TR]
[TR]
[TD]Savings[/TD]
[TD="align: right"]-21,500[/TD]
[TD="align: right"]-15.36%[/TD]
[/TR]
[TR]
[TD]

401k​

[/TD]
[TD="align: right"]-16,000[/TD]
[TD="align: right"]-11.43%[/TD]
[/TR]
[TR]
[TD]

Roth​

[/TD]
[TD="align: right"]-5,500[/TD]
[TD="align: right"]-3.93%[/TD]
[/TR]
[TR]
[TD]Net Income[/TD]
[TD="align: right"]38,500[/TD]
[TD="align: right"]27.50%[/TD]
[/TR]
</tbody>[/TABLE]



Very much appreciate any help provided.

Thank you,
Clayton_

 

[Sinon]

The problem is that excel has no way of knowing that your income is 100% of all the numbers, especially since adding it all up doesn't get you that number (excel would add all the Income lines but also the Expense and Savings lines) so it would end up at 38.5K total amount, i.e. 100%.

To counteract this, you need to add a formula in your data table.

With your example data as above, add another column with this formula:

=C2/SUMIF($A$2:$A$9,"Income",$C$2:$C$9)

This will give you the desired % in the table. To get these to the pivot, simply add this new column into the Values field of the pivot and format it as %.