What-if analysis using the right level of granularity in PowerPivot
April 22, 2012 2 Comments
PowerPivot offers a big advantage when conducting ‘what-if’ scenario analysis: the ability to specify ad hoc values in a report and use them – in conjunction with the existing data – to generate personalized outputs without ever having to leave the workbook. (As Cathy Dumas pointed out in the comments section of this entry, SSAS Tabular models can also include ‘ad hoc’ data by giving the ability to directly paste it into the model; PowerPivot, interestingly, makes the job even easier by the use of ‘linked tables’).
As an example, look at the following data model (based on the Adventure Works database):
Imagine that we wanted to motivate our sales force to increase orders by giving them a special bonus computed based on the average monthly sales. We are investigating what the actual dollar pay-out will be, at different rates (I am keeping the scenario very simple)
So we can just type in Excel the different percentages we are exploring. Something like:
At this point, it is necessary to import these values into PowerPivot, by using the ‘Create Linked table’ option. This new table will have no relationships to the other existing ones in the PowerPivot data model: and we want to keep that configuration, as there are no common keys with the other tables.
We can grab the Pay-out Rate value selected by the user using the VALUES function:
User Selected Rate:=VALUES(‘Pay-out Rate'[Pay-out Rate])
We can include this value against the sales amount to compute the bonus pay-out:
=IF(HASONEVALUE(‘Pay-out Rate'[Pay-out Rate]) &&
AVERAGE( FactResellerSales[SalesAmount] ) *
VALUES(‘Pay-out Rate'[Pay-out Rate])
Before we starting complaining how cheap management is, lets examine why this initial calculation is incorrect. Even thought the calculation changes based on user selection, as you may recall, the bonus should be based of the monthly averages. Our fact table stores data at the daily sales by product granularity. The calculation is incorrect as the average is generated at this level (by product by day); The correct approach, instead, should not take into account product in the computation – it should only compute the monthly average and then apply the rate selected by the user. The sum of each output at the monthly level will determine the year’s bonus.
With DAX, this can be achieved by leveraging the power of the SUMMARIZE function. By generating an on-the-fly table of the distinct months that are currently in context, we can then average data for those months and then apply the given rate.
Bonus – Correct:=IF(
HASONEVALUE(‘Pay-out Rate'[Pay-out Rate]) &&
NOT(HASONEVALUE(DimDate[EnglishMonthName]) ) ,
SUM(FactResellerSales[SalesAmount] ) /
COUNTROWS( VALUES (DimDate[DateKey] ) ) ),
[Sales] * VALUES(‘Pay-out Rate'[Pay-out Rate] )
Again, we ensure the calculation is correct by ignoring the actual granularity that exists in the fact table; instead we compute the monthly average as the sum of amount over the day count, after which we include the user selected ‘scenario’ rate in the computation. The power of this technique is that we can affect the data at the appropriate level, and then let it roll-up to higher levels.
If more scenarios need to be added, it is just a matter of typing the new value in the spreadsheet table, re-opening the PowerPivot window (which will automatically import the new values) and hitting refresh in the ‘Data’ tab in Excel. The new ‘scenario’ rate will now be available for reporting:
The combination of these fairly simple techniques can be useful to generate very powerful, scenario driven what-if analytics. This is what self-service BI is all about!