BLOG

Scenario analysis with Power BI

06.11.2017 Lukas Lötters

How to compare two or even more scenarios in Power BI ? At the moment there is no build-in feature in Power BI that allows you to dynamically compare scenarios, but with DAX this is no problem. In this blog post I will show you how to set up your data model and the formulars for this task.

The Data

First of all we need some data, I use generic data here, but the example can easyly be converted to your data.  The data in this example looks like this: Every row is one Product at one date for one scenario with the coressponding value. As you can see in the screenshot there are 2 products and 3 scenarios for each product. The formulars shown below also work for more products or scenarios. The story behind the data could be you that you are the analyst and have to compare three scenarios, which show the future develeopment of the sales amount for the upcoming year.

Scenario analysis with power bi

Compare Scenarios

Let’s start by creating a visual that helps us to see the difference between the three scenarios. So create a line chart and put the ‚Date‘ on the axis, the‘ Scenario‘ on the legend and ‚Value‘ on value. With a simple slicer on the top for ‚Product‘ we can now simply select either one of the products or both together. Out of the box you can now compare the different scenarios with each other. Click on the GIF below to see it in action.

Scenario

From the visual above, we can see that S1 and S2 appear to move up and down around an unknown expected value. S3 seems to linearly increase every month. So maybe the truth lies in the combination of all three scenarios. For example if I’m a little bit risk avers I might be intereset in the minimal sales amount of all scenarios. Or if I am an optimstic guy, I can look for the maximal sales amount of all three scenarios combined. This can be easily solved with DAX by using this formulars.

  1. Max Curve =SUMX ( ‚Scenario Long‘; MAX ( ‚Scenario Long‘[Value] ) )
  2. AverageCurve =SUMX ( ‚Scenario Long‘; AVERAGE ( ‚Scenario Long‘[Value] ) )
  3. MinCurve =SUMX ( ‚Scenario Long‘; MIN ( ‚Scenario Long‘[Value] ) )

Average Lines

Dynamic scenario comparison

Many users want to be able to dynamically compare scenarios. To do this in Power BI you need a little trick in DAX. The simplest way to do this is to use a parameter table. Parameter tables are tables independent of the actual data model, which are only integrated via measures. The parameter table only contains the unique names of our scenarios. The easiet way to create parameter tables in our case is to create 2 custom tables in the model by using the distinct function.Create new Table

  1. Base =DISTINCT ( ‚Scenario Long‘[Scenario] )
  2. Reference =DISTINCT ( ‚Scenario Long‘[Scenario] )

Model
We now create measures that check if one of the parameter tables is filtered. The measure will then only sum up the corresponding scenario from the content table (Scenario Long). On SQL BI their is a very good explanation how to integrate a parameter table into a measure.

  1. Base Scenario =CALCULATE (
  2.     SUM ( ‚Scenario Long‘[Value] );
  3.     TREATAS ( VALUES ( Base[B_Scenario] ); ‚Scenario Long‘[Scenario] ))
  4. ReferenceScenario =CALCULATE (
  5.     SUM ( ‚Scenario Long‘[Value] );
  6.     TREATAS ( VALUES ( Reference[R_Scenario] ); ‚Scenario Long‘[Scenario] ))

With a simple subtraction we can calculate the difference between both scenarios. Click on the GIF below to see everything in action.

  1. Dif. between Reference and Base = [Reference Scenario] [Base Scenario]

 

Dynamic Scenario COmparison

Conclusion

So with all this in place we can now easily compare two scenarios with each other. I puplished this report example, you can view it here: Compare two Scenarios. I think this use case is another example how powerful the data modeling engine behind Power BI is and how easily it can be used to extend the technical possibilities. I hope you find this helpful.

 

Your email address will not be published. Required fields are marked *

Join #teamoraylispeople

Gestalte mit uns
die Welt der Daten