Power Pivot Principles: Summarising a Budget

Welcome back to the Power Pivot Principles blog. This week, we’ll examine an example of how to summarise a company’s budget in Power Pivot.

Let’s assume a company XYZ has a Budget table they wish to summarise. Their budget consists of three worksheets. The first is the Budget table, viz.

The second is the Transaction table:

The final worksheet comprises the Charts of Accounts (COA) table:

To begin, we must add all three worksheets to the Data Model in Power Pivot by clicking on ‘Add to Data Model’ on the ‘Power Pivot’ tab of the Ribbon.

To facilitate the summarising of the Budget table, it important to define relationships between these tables. These relationships will allow us to us the RELATED function to extract values from different columns of another worksheet. We can also add a Calendar Table, which will later be used in a PivotTable too.

By switching to the ‘Diagram View’ in the Home tab, we can drag the ‘Account Number’ field to connect the COA and Budget tables, as well as link ‘EOM_Date’ to ‘Date’, to connect the Calendar and Budget tables.

Switching back to ‘Data View’, we may add new calculated columns to identify ‘Account_Name’ using the follow code:

The next step in the process is to transform the table into a Pivot Table, by selecting the option from the Home tab:

In the ‘PivotTable Fields’, drop the ‘Month’ from ‘Date Hierarchy’ in Calendar table to ‘Columns’, ‘Account_Name’ from Budget table to ‘Rows’ and ‘Amount’ from Budget table to ‘Values’.

Click on the drop down menu next to ‘Sum of Amount’ to select ‘Value Field Settings’:

Click on ‘Number Format’:

Finally, select ‘Currency’, reducing the figures displayed to two decimal places and hit OK.

You will now have a summary of the budget, viz.

That’s it for this week!


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