Power Pivot Principles: Budget vs Actuals

Welcome back to the Power Pivot Principles blog. This week, we’ll create a quarterly Budget vs Actuals table based on different Account aggregation.

To begin with, I have a Transaction table that contain “Actual” values for different accounts in the year 2020, viz.

Before I bring in this Actuals data, I must create a relationship between the COA (chart of accounts) table and the Transaction table based upon the Account_Number column. Additionally, there should be another link between the Calendar table and Transaction table based upon the Date column. This is an addition to the relationships created earlier and would look as follows:

Now that I have my relationships created, I can rename the Amount columns in the Budget and Transaction tables by prefixing them with their respective table names and then hit Refresh on the Data tab.

Further, to summarise the Budget vs Actual data, I select the following fields in the Pivot Table:

We have a quarterly Budget vs Actuals table prepared based upon different Account aggregations:

While most of you are wondering why some of the aggregations have negative values for Budget data but positive values for Actuals data, that’s this week’s cliff-hanger. We will cover that next week!

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.