Performing "What if" type modelling analysis in PowerPivot

Question

Hi,

I am currently engaged in a project to develop a BI Solution for a client using SQL Server 2012. One of their requirements is to be able to to perform quite complex "what-if" type modelling on the data. I thought that the combination of PowerPivot and Excel would be ideal for this however being new to PowerPivot and DAX, I am finding the implementation a bit tricky.

As an example of what is required. one report we need to develop will show attendance rates at a school. The user would like the ability to model what happens if each student were to attend for a certain number of additional days each term (ensuring that the total is not greater than the total number of days in the term) and see how that affects the overall attendance rates. Effectively, this means that we need to increment a value in each row of the table by a user entered amount and maintain correct totals subtotals etc.

At first, I assumed this we be simply a matter of adding a calculated column into the PowerPivot model that would increment the number of days by an amount from a slicer or filter. But it seems it is not that easy. I have been able to get the value of a slicer in to a measure but can't find any way of applying this to row level calculations. Applying the calculations usings measures works when fully detailed data is displayed but not on summarised data.

I would appreciate any advice on:

• Is what I want to do possible with PowerPivot?
• Is there a work around to achieve this?
• If the answer to the above is Yes, then what is the work around?
• Are there any other technologies in the SQL Server 2012 toolkit (or elsewhere) that might be better for achieving this?

Any assistance would be greatly appreciated.

Thanks,

Shaun

Answer 1

Shaun,

I think the way I'd approach this is to use the CUBEVALUE() and related functions in the worksheet rather than just a pivot table.  Then perhaps you could get the rows to fill-down with the incremented # days (such as if I enter 3 then you fill down 3, 6, 9, 12, ...).  Then across the rows if you could use CUBEMEMBER() to extract the matching dimension member for whatever calculated number of days, and nest that inside a CUBEVALUE() call to extract a calculation based on that member.

That probably sounds a little confusing if you've not used these Excel functions before, but I've been able to tackle a lot of quirky custom things like this using these functions...they can be remarkably versatile.

If you haven't any experience with them, just use the "convert to formulas" command on an existing pivot table and then look at what happens in each cell's formula.  Then work on developing your own functions to extract tuples as you need them to provide the solution.  It takes a little practice to get the hange of it at first but learning this technique can really extend what you can do with PowerPivot and SSAS quite a lot.

Good luck with your solution! 

---

Rob Kerr SQL Server MVP CTO, BlueGranite

Answer 2

 I have been able to get the value of a slicer in to a measure but can't find any way of applying this to row level calculations. Applying the calculations using measures works when fully detailed data is displayed but not on summarized data.

Shaun, it is possible to get the value from a slicer selection into a calculated column.  [See correction from Javier. This is only true for measures, not calculated columns.]

Check out solution #2 in blog post below which demonstrates the general technique. [only relevant to measures, not calculated columns]

http://www.powerpivotpro.com/2012/01/comparing-scientific-and-other-data-across-trials/

Answer 3

Ruve1k, one small correction here:  his technique describes how to use a slicer selection on a measure, not a calculated column.  Calculated columns can't really take into account slicer or filter selection, as they are computed during processing time and their values are persisted.

Shaun, I do believe you can achieve what you need with PowerPivot.   Given what was stated before, you must use a measure, but thanks to iterative calculations (like SUMX) you can execute the expression at different levels of granularity.   For example, the following expression will add up all values after doing first a row by row calculation on them

SUMX(Table1, Table1[amount] * (1- Table1[tax] ) )

Notice the first parameter takes a table as input.  That table can be an expression that yields an output table at a different level of granularity, if need be. For example:

SUMX(VALUES(Table1[category]), CALCULATE( AVERAGE(Table1[amount]) ) * (1- VALUES(Table2[tax brakets])  ) )

In the case above, we are changing the calculation from the most granural level (leaf level), to a higher level (category in this case).

---

Javier Guillen
http://javierguillen.wordpress.com/