Using DAX for Budget Allocation

Andrew Smith Posted on

www.daxpatterns.com is Marco Russo’s and Alberto Ferrari’s website for cataloging generic DAX solutions to common business problems.

In this post I’m deconstructing a DAX measure used by their Budget Allocation pattern, to understand exactly how it works. Fundamentally it’s an expression that allocates a budget set at a low granularity (e.g. year) to time periods of a higher granularity (e.g. month).

The measure is this:

daxbudgetallocationquery

and a minimal model that it runs over in SSAS Tabular is this:

daxbudgetallocation

Note that for the Budget table data is stored at year granularity only, and that unlike the Sales table, it has no relationship with the Date table. This is because a relationship with Date at the year level would be many-to-many whereas DAX models only explicitly support many-to-one relationships. Note also that the Budget table is hidden from users – its contents are only accessible via measures that are exposed to users.

4 measures can be seen in this model. The [IsBudgetValid] measure assigned to the Date table is this:

IsBudgetValid:=
NOT (
     ISFILTERED ( ‘Date'[Date] ) ||
     ISFILTERED ( ‘Date'[Month] )
)

which says that if either the [Date] (i.e. day) column or [Month] column of the Date table are being filtered on directly in the client query then values in the Budget table are not valid for comparison (since budget data is year only). It’s used in the guard condition in the IF() statement of the [Budget] measure at the top of this post, such that if it’s True then the stored year budget data is returned, else higher granularity budget allocations are calculated.

The [Total Sales] measure is just this:

Total Sales:=SUM([SalesAmount])

and the [Budget] measure is the one shown at the top of this post and that’s explained in detail below. The final measure is [Var%], which is defined as:

Var%:=DIVIDE ( [Total Sales] – [Budget], [Budget] )

This performs a standard variance calculation, where the DAX() DIVIDE() function in this example will return BLANK() if the divisor is zero.

Sample output of these measures in a Power BI matrix is:

daxbudgetallocationpivottable

So looking at the [Budget] measure definition from above and working from the top down, the IF() condition (as previously explained) is deciding whether to return budget data stored at year granularity or to calculate budget allocations at a higher granularity such as quarter or month.

The SUMX() and VALUES() functions are then used to deal with the situation where multiple years have been selected on the pivot, e.g. in the situation where months (and no years) are on rows and products are on columns. SUMX() will add together the allocated value generated in its 2nd argument for each year in the pivot, and return the final sum (e.g. the total budget for January 2006 and January 2007).

The expression in the second argument of SUMX() first calculates the ratio of working days in the current Date context to the number of working days for the whole year pertaining to the current Date context. This is a reasonably straight forwards calculation with the divisor using ALLEXCEPT() to clear the filter context of the Date table of all filters except for the Year column filter. So this will work whether the query granularity is month or quarter, for example.

This ratio is then multiplied by the stored budget value for the year in the current filter context. The SUM() in this final CALCULATE() function will only ever be summing one number, since the SUMX()/VALUES() combination will just be supplying 1 year each time around the SUMX() iteration. And the use of FILTER() is required since having columns on both sides of the filtering equality expression is more complicated than CALCULATE() can handle by default. Finally, since FILTER() has a column reference as its first argument, this needs to be wrapped in a function that results in a table. ALL() does the job here, although it won’t ever be clearing any filters on the Budget[Year] column since the Budget table is hidden from users and so cannot directly be filtered by them.

In summary, the budget measure calculation described here has a flexible structure than can be used as the basis for many budget allocation scenarios. Russo and Ferrari describe several use cases for it on their website here.