Power Pivot Principles: Credit Transactions

Welcome back to the Power Pivot Principles blog. This week, we’ll continue where we left off last week, revealing “the truth” about the different negative and positive values.

To refresh your memory, I would like to first show you the results from last week, viz.

Yes, I remember leaving you guys on a precipice. The values for some aggregations of the Budget data are negative, whereas they are positive for Actual’s data. The reason for this difference is that Actual transactions are recorded as absolute values. Wondering what to do next? Let me give a hint by refreshing your memory, viz.

I know I made it obvious, but yes, the ‘Is Credit’ column is going to help us determine the difference between negative and positive values.  That is, credit transactions (determined by the ‘Y’) should have negative values.

To incorporate for the credit transactions, I will create a new calculated column:

I have named the column as Actual_Amount and it looks like, viz.

Now, I can replace the Transaction_Amount with Actual_Amount in my original table from last week.

Finally, my updated quarterly Budget vs Actuals table looks like, viz.

That’s it for this week!


Come back next week to see how we use the disconnected table for performing a sensitivity analysis by staying tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.