Pivot Table Calculated Total

[jstevens]

I have a pivot table that returns yearly totals of Revenue + Expenses (1000 + 500 = 1500). Revenue and Expenses are both positive which is returning the incorrect yearly total. In this example the yearly total should be 1000 - 500 = 500.

I'm not sure how to change the calculation to return the correct value.

Your thoughts are appreciated.

[HansV]

Could you attach a small sample workbook?

[jstevens]

Hans,

Here is a sample workbook.

Pivot.xlsx

[HansV]

I would convert all expense amounts to be negative (just like you need to do in Excel's financial functions).

S3450.png

[jstevens]

Hans,

I tried your suggestion prior to posting. That corrects the math in the pivot table however the chart changes: expenses shown as negative instead of positive. Is there a way to multiply expenses in the chart by negative 1?

EL_94.PNG

[HansV]

That's the way financial stuff works: income is positive, spending is negative...

If you don't like this, you'd have to create your own table using formulas instead of a pivot table.

[jstevens]

Thanks Hans!

[jstevens]

Hans,

I found a solution utilizing Power Pivot which has been around since Excel 2010. I modified my sample file and put the solution on the sheet named "Data Tables".

Pivot.xlsx

Looks like jstevens learned something new today

[HansV]

Great!