Excel 2024: Compare Budget Versus Actual via Power Pivot

Budgets are done at the top level revenue by product line by region by month. Actuals accumulate slowly over time invoice by invoice, line item by line item. Comparing the small Budget file to the voluminous Actual data has been a pain forever. I love this trick from Rob Collie, aka PowerPivotPro.com.

There is no VLOOKUP in the world that will ever let you match these two data sets. But, thanks to Power Pivot (aka the Data Model in Excel 2013+), this becomes easy.

You need to create tiny little tables that I call joiners to link the two larger data sets. In my case, Product, Region, and Date are in common between the two tables. The Product table is a tiny four-cell table. Ditto for Region. Create each of those by copying data from one table and using Remove Duplicates.

Illustration: George Berlin

The calendar table on the right was actually tougher to create. The budget data has one row per month, always falling on the end of the month. The invoice data shows daily dates, usually weekdays. So, I had to copy the Date field from both data sets into a single column and then remove duplicates to make sure that all dates are represented. I then used =TEXT(J4,"YYYY-MM") to create a Month column from the daily dates.

If you don't have the full Power Pivot add-in, you need to create a pivot table from the Budget table and select the checkbox for Add This Data to the Data Model.

As discussed in the previous tip, as you add fields to the pivot table, you will have to define six relationships. While you could do this with six visits to the Create Relationship dialog, I fired up my Power Pivot add-in and used the diagram view to define the six relationships.

Here is the key to making all of this work: You are free to use the numeric fields from Budget and from Actual. But if you want to show Region, Product, or Month in the pivot table, they must come from the joiner tables!

Here is a pivot table with data coming from five tables. Column A is coming from the Region joiner. Row 2 is coming from the Calendar joiner. The Product slicer is from the Product joiner. The Budget numbers come from the Budget table, and the Actual numbers come from the Invoice table.

This works because the joiner tables apply filters to the Budget and Actual table. It is a beautiful technique and shows that Power Pivot is not just for big data.

Bonus Tip: Portable Formulas

You can use the DAX formula language to create new calculated fields. From the Power Pivot tab in the Ribbon, choose Measures, New Measure. If you don't have the Power Pivot tab in the Ribbon, you can right-click the Table name in the PivotTable Fields pane and choose Add Measure.

Give the field a name, such as Variance. When you go to type the formula, type =[. As soon as you type the square bracket, Excel gives you a list of fields to choose from. Note that you can also assign a numeric format to these calculated fields. Wouldn't it be great if regular pivot tables brought the numeric formatting from the underlying data?

In the next calculation, VariancePercent is reusing the Variance field that you just defined.

So far, you've added several calculated fields to the pivot table, as shown below.

But you don't have to leave any of those fields in the pivot table. If your manager only cares about the variance percentage, you can remove all of the other numeric fields.

Note that the DAX in this bonus tip is barely scratching the surface of what is possible. If you want to explore Power Pivot, you need to get a copy of Supercharge Power BI by Matt Allington.

Thanks to Rob Collie for teaching me this feature. Find Rob at www.PowerPivotPro.com.

Bonus Tip: Text in the Values of a Pivot Table

Another amazing use for a measure in a Data Model pivot table is to use the CONCATENATEX function to move text into the values area of a pivot table.

In this data set, there is an original and revised value for each sales rep.

'

Insert a pivot table and check the box for Add This Data To The Data Model. Drag Rep to the Rows and Version to Columns.

'

The Grand Totals get really ugly, so you should remove them now. On the Design tab, use Grand Totals, Off For Rows and Columns.

'

In the Pivot Table Fields panel, right-click the Table name and choose Add Measure.

The formula for the measure is =CONCATENATEX(Values(Table1[Code]),Table1[Code],", "). The VALUES function makes sure that you don't get duplicate values in the answer.

After defining the measure, drag the measure to the Values area. In this case, each cell only has one value.

However, if you rearrange the pivot table, you might have multiple values joined in a cell.

Caution: A cell may not contain more than 32,768 characters. If you have a large data set, it is possible that this Grand Total of this measure will be more than 32,768 characters. The Excel team never anticipated that a pivot table cell would contain more than this many characters, but thanks to DAX and CONCATENATEX, it can happen. When it does happen, Excel can not draw the pivot table. But - there is no error message - the pivot table simply stops updating until you get rid of the Grand Total or somehow make the largest cell be less than 32,768 characters.