Actual versus budget model and measures

This example provides support for actual versus budget analysis in a model containing different grains.

  1. Create the bridge tables
    • Budget Product Categories and Budget Dates bridge tables are added to the model at the grain of the budget
    • Each table contains the unique values of the dimension at the grain of the Budget table
These bridge tables can potentially leverage the existing M queries in the model used to load the dimension tables. For example, the following M expression references the Date table query as it's source and selects only the distinct values of the Calendar Yr-Mo column. If this is the grain of the budget table, this single column table could be used as the bridge table.

let Source = Date,
      YearMonthColumn = Table.SelectColumns(Source,{"Calendar Yr-Mo"}),
      RemoveDuplicates = Table.Distinct(YearMonthColumn)
in RemoveDuplicates

Alternatively, a simple SQL view could be created ('Select Distinct [Calendar Yr-Mo] From dbo.DimDate') that selects the distinct values of the column and this view could be accessed from a new bridge table M query. 
  1. Create the relationships
    • Create one-to-many relationships with single direction cross filtering from the bridge tables (Budget Dates, Budget Product Categories) to the Internet Sales Budget table
    • Create many-to-one relationships with bidirectional cross filtering between the dimension tables (Date, Product) to their respective bridge tables (Budget Dates, Budget Product Categories)
Internet Sales versus Budget Data Model
  • The two bidirectional relationships highlighted in the image allow filters on the Date and Product tables to propagate to the Internet Sales Budget table in addition to the Internet Sales fact table
  • The only remaining steps requiring some level of code is to avoid invalid filter contexts. For example, the Internet Sales fact table can of course be filtered by individual products and dates but this granularity isn't available for the Budget and thus a blank value should be returned. 
  1. Create a measure to test for filter context 
    • The following measure checks whether filters have been applied at higher grains than the budget table
Budget Filter Test = 
 VAR CustomerFilter = ISCROSSFILTERED(Customer) 
 VAR ProductGrain = 
  ISFILTERED('Product'[Product Class]) || ISFILTERED('Product'[Product Color]) || 
  ISFILTERED('Product'[Product Subcategory]) || ISFILTERED('Product'[Product Name])
 VAR DateGrain = 
  ISFILTERED('Date'[Calendar Yr-Wk]) || ISFILTERED('Date'[Date]) || ISFILTERED('Date'[Wk End Date])
RETURN
IF(CustomerFilter = TRUE() || ProductGrain = TRUE() || DateGrain =TRUE(), 
 "Higher Grain", "Common Grain")
  • The filter test is used to protect against invalid sales to budget reports with different filters applied to each fact table
  1. Create the budget measures
    • Create a budget measure and an actual to budget variance measure
Internet Sales Budget = 
 VAR BudgetSales = sum('Internet Sales Budget'[Online Sales Budget])
RETURN
IF([Budget Filter Test] = "Common Grain", BudgetSales, BLANK()) 

Internet Sales Actual to Budget Variance = 
IF(ISBLANK([Internet Sales Budget]),BLANK(), [Internet Sales] - [Internet Sales Budget])
  • In this scenario, the requirement is to only test for a common grain and return a blank otherwise. 
It's possible to build allocation logic into the DAX budget measure to account for higher grains. In general, these allocation rules are better implemented in the budget process itself or via ETL tools and query languages such as SQL and M. 
  1. Hide the bridge tables, budget columns, and the filter test metric from the Report View
  2. Validate and visualize actual versus budget
    • Create sample visualizations which filter budget and sales by Product and Date dimensions
Internet Sales Actual versus Budget by Product Category and Year-Month
Matrix visuals provide functionality similar to Excel pivot tables and are therefore a good choice for Budget versus Actuals. 
上一章目录下一章