excelisfun - MSPTDA 26: Budget vs Actual in Power Pivot & Power BI Desktop, DAX & Data Model

Short Summary

Main recommendation: To compare budgeted amounts with actuals in Power Pivot, build a data model with two fact tables (budget and transactions) and shared dimension tables (Product and Date), then create DAX measures for Budgeted Sales, Actual, Variance, and Variance %. The video shows that this approach produces product, month, year, and grand totals that a worksheet-formula approach makes harder to build. The key modeling step is relating the Date table to the budget table's end-of-month column so that month filters flow correctly to both fact tables.

Key Points

• A worksheet-formula comparison works for simple product-by-month lines but becomes complicated when you need product totals, yearly totals, and monthly totals.
• A PivotTable built on the data model handles those totals automatically and can be pivoted, for example by dragging Product above Year.
• The budget table (grain: month and product) and the transactions table (grain: transaction date and product) are two fact tables, so they need to be linked through shared dimensions.
• Product filters both fact tables directly through a one-to-many relationship, since each product appears many times in both tables.
• Filtering by month works because the budget table's End of Month column acts as the many side of the Date relationship, so only the last day of each month flows to the budget table.
• Customer is not a valid slicer for this report because budgets were not set by customer, so filtering by customer produces misleading actual figures.

Details Worth Noting

• Concrete instructions:
  • Import the budget table and the transaction, date, product, and customer tables into the data model (Add to Data Model in Power Pivot).
  • In Diagram View, relate Product (one side) to both fact tables' product columns, and relate Date (one side) to the budget table's End of Month column (many side).
  • In the budget table, create the measure Budgeted Sales := SUM(F_Budget[Budget]) and apply number formatting.
  • In the transactions table, create Sales := SUM(F_Transactions[Sales]) with number formatting, then Var := [Sales] - [Budgeted Sales], then Var % := DIVIDE([Var], [Budgeted Sales]) with percent formatting.
  • Hide unneeded columns from client tools: in the budget table, hide all columns except Budgeted Sales, plus Date and Month Number; in the transactions table, hide all columns.
  • Insert a PivotTable from the data model, then place Product, Year, and Month in Rows and check Actual, Budgeted, Variance, and Variance %.
  • Do not add Customer to the PivotTable or as a slicer for this report, and delete any customer relationship you add by mistake (Delete from Model).
• The example shows an unfiltered grand overall variance of about 200k below budget, visible in the measure grid before slicing by product or month.
• The video is part of the MSPTDA series and uses a downloadable sample file linked in its description.

Bottom Line

A Power Pivot data model with a properly related Date table and shared Product dimension lets budget and actual figures filter together, so you can build reliable budget-versus-actual reports with automatic totals.