Forum Discussion
How to show the difference between subtotals in a pivot table
I have a typical pivot table of Income and Expense accounts by cost center, with subtotals for each cost center. Problem is that the expenses are positive numbers coming from the source which is a standard treatment for financial systems. So the cost center subtotals are adding income plus expense instead of taking the difference of income minus expense. How can I resolve this without turning all the expenses into negative numbers?
2 Replies
Art_W4 If the source produces expenses as positive numbers (debits) then make sure that income items are included as negatives (credits)? That would be the correct accounting style. Then change the sign for every entry in the amount column (add a column amount * -1) and base the pivot table on that one. Or just change the sign for all expense items before you combine the expense data with the income data. I don't believe you can do this within a regular pivot table. In power pivot you could create a measure that changes the sign for all transactions in the OPEX category.
In short, clean-up/manipulate the raw data first rather than trying to fix an incorrect summary.
Thanks. I was trying not to change the sign of the expense items but it sounds like that is the only option to get the totals to be correct.
I tried to introduce a Calculated Item consisting of Income minus Expense fields, but that seemed to hang Excel up in a "not responding" state for many minutes until I had to kill Excel.
