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.

Type	Description	Amount
Income	Salary	100,000.00
Income	Bonus	35,000.00
Income	Consulting	5,000.00
Expense	Mortgage	(18,000.00)
Expense	General Living	(30,000.00)
Expense	Taxes	(32,000.00)
Savings	401k	(16,000.00)
Savings	Roth	(5,500.00)

<tbody>
</tbody>

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.

Row Labels	Sum of Amount	Percent of Income
Income	140,000	100.00%
Bonus​	35,000	25.00%
Consulting​	5,000	3.57%
Salary​	100,000	71.43%
Expense	-80,000	-57.14%
General Living​	-30,000	-21.43%
Mortgage​	-18,000	-12.86%
Taxes​	-32,000	-22.86%
Savings	-21,500	-15.36%
401k​	-16,000	-11.43%
Roth​	-5,500	-3.93%
Net Income	38,500	27.50%

<tbody>
</tbody>

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 %.