Allocations in PowerPivot Using DAX

Although I didn’t mention it, the inspiration for my last post on DAX Closing Balances was the excellent MDX Solutions 2nd Edition. Whilst flicking through the book a while back, I though it would be interesting to see how DAX and PowerPivot could handle the so called ‘Common Calculations and Selections in MDX’.

This post continues that theme, focusing on one calculation in particular that’s in the aforementioned MDX book, namely ‘Unweighted Allocations down the Hierarchy’. Essentially this business problem is dealing with allocating data that is entered at a higher level (e.g. Quarter) down to a lower level (e.g. Day). It varies from business to business what the users actually want to see at the day level in this situation. Some expect to see the Quarter amount, some want to see a blank value and then some users want the amount in the quarters allocated down to the day level.

If the expectation is that data should be allocated, then one way of doing the allocation is to use a ratio. In the MDX book, this is achieved by the following MDX:

1.0 / 
Descendants(
            Ancestor(
                    [Date].[Calendar].CurrentMember,
                    [Date].[Calendar].[Calendar Quarter]
                    ),
            [Date].[Calendar].CurrentMember.Level,
            SELF
            ).Count

If we put this into a query and run it against the Calendar hierarchy in AdventureWorksDW we get the following results:

Therefore our quarter-entered value can simply be multiplied by this ratio to get the correct value at each level in the Calendar hierarchy.

PowerPivot

So the challenge is how to be able to do this in PowerPivot. I started off by looking for some data in AdventureWorksDW that would fit the bill. A good candidate seems to be the FactSalesQuota table, which I imported into PowerPivot along with its related dimensions:

Once in the pivot table, I wanted to be able to calculate the correct ratio at the daily and monthly levels. For the daily level this is 1 / 90, as there are 90 days in the Q1 that year, and for the month level it is 1/3 as there are 3 months in the quarter.

Given that there’s no MDX style Ancestor() function in DAX, I ended up having to have a different calculation at different levels. Something that I learnt from this forum post is that you need to use IF() in order to isolate a calculation at a different level. Therefore the DAX that I came up with was:

=IF( COUNTROWS(VALUES(‘DimDate'[FullDateAlternateKey])) = 1,

1 / CALCULATE( COUNTROWS(‘DimDate’), ALL(‘DimDate'[FullDateAlternateKey], ‘DimDate'[EnglishMonthName]) ),

1 / CALCULATE( COUNTROWS( DISTINCT( ‘DimDate'[EnglishMonthName]) ), ALL(‘DimDate'[EnglishMonthName]) )

)

The COUNTROWS(VALUES(‘DimDate'[FullDateAlternateKey])) = 1 allows me to check that we’re at the day level, and if we are at the day level, then count the number of days in the quarter. Otherwise, if we’re at the month level we calculate the number of months in the current quarter. When this DAX is put into a new measure we get the following results:

This produces the same results as the MDX statement. The next step was then to apply this ratio to the Sales Amount Quota for the current quarter. This was achieved using the following DAX:

=IF(

COUNTROWS( VALUES(‘DimDate'[FullDateAlternateKey]) ) < 90,

CALCULATE( Sum(‘FactSalesQuota'[SalesAmountQuota]),
All(‘DimDate'[FullDateAlternateKey], ‘DimDate'[EnglishMonthName]) ) * ‘FactSalesQuota'[Ratio],

Sum(‘FactSalesQuota'[SalesAmountQuota])

)

The < 90 at the start of the statement allows me to check if we’re at the day level or the month level. If we’re at either of these levels, then we want to pick up the Quarter-level SalesAmountQuota and multiply by the ratio. Otherwise, at the Quarter and Year levels, we just apply the original SalesAmountQuota value. This produces the following results:

Conclusion

The use of the MDX Scope statement would have been nice to pinpoint exactly where I wanted the calculation to occur, but then again, the use of IF() keeps it quite simple, which is better for an Excel power user. Whilst MDX is very very powerful, can you imagine a business user getting to grips with MDX statements such as Scope or Freeze?

The bottom line is that the DAX calculation produces exactly what I want – the new AllocatedSalesQuota measure is correct at all levels in the Calendar hierarchy. Even with an IF() statement, like everything else in PowerPivot, the calculation performs very quickly.