Do you want to see how advanced you can get with Power BI? In this tutorial, I want to show you my favourite way to use Power BI. You can do some amazing work around scenario analysis by integrating what if parameters in Power BI — things like sales, profits, or transactions. You may watch the full video of this tutorial at the bottom of this blog.
Not just because of the insights you can find, but also the ease which you can implement this analysis compared to doing this with historical tools like Excel. I seriously do not even know how you would get close to implementing this in Excel without having to do something outrageously complex.
You can essentially at any point in time predict what might occur in the future or even showcase a range of things that might occur in the future if scenarios play out as you perceive they might. You can also overlay this on historical information as well. So you could have a look at what would have happened if something did occur.
Incorporating Multiple What If Scenarios All At Once
In this blog post, I want to show another really cool example on how you can incorporate multiple what if parameters in Power BI all at once. You will be able to see how all of these individuals what if parameters in Power BI can impact multiple scenarios.
In reality, this is something that might happen. For example, in one scenario you might want to see what will happen if you increase the price and demand stays the same or decreases. You might also want to see a Best Case, an Ok Case, and a Worse Case scenario while working with all these different parameters.
Working With Three Scenarios
I’m going to show you show you how I’ve created a model that enables you to analyze all of these all at once. I have a Price Change parameter, a Demand Change parameter, and a Cost Change parameter. I also have the scenarios here: Best Case, Ok Case, and Worse Case.
You can see that in the Best Case, as we increase prices, the demand increases, which is quite surprising. But this has happened before where people may view the increased price as a more premium product so they go buy it more.
In the Ok Case, if we increase the prices nothing happens to demand, but the cost decreases by 1%.
In the Worse Case, we don’t change the price but demand just falls and our costs increase.
We also have a date slicer on the upper right corner and the cumulative impact of these Best/Ok/Worse cases to our actuals at the bottom chart.
We have our actual profits or forecasted profits, and then I’ve gone ahead and shocked them with our multiple what-if scenarios to see the impact throughout the year if we implement these changes.
Creating The Table
This is just an example I set up but what I want to get across are the techniques it took us to get here. The first thing to do is to create a table that listed out the Best Case, Ok Case, and Worse Case, as well as the parameters and scenarios involved such as Price Change, Demand Change, and Cost Change.
Working With HASONEVALUE
I needed to work with a pattern based on whatever scenario selection I make here. I used a pattern that I have used in a lot of scenario-type analysis where IF HASONEVALUE or if one of the scenarios are selected, then equal to that scenario price change. I’ve used MIN to pick up what the actual price change is. There should be only one value when something is selected because our table has only one line. If nothing is selected, the price, demand, and cost change should all equal to 0.
Scenario Profits Using SUMX
The next step is the integral logic part and this is the formula I used to integrate all of these changes to a particular scenario.
I have used this approach in my multi-layered scenario analysis. By using the SUMX formula, it’s creating the shocks based on these values. Let’s break the formula down into two parts: the revenue part and the costs part.
For the revenue part, I’m going with Sales Order Quantity multiplied by the Demands Change because if the demand increases, then we’re going to sell more of the quantity. Then we’re going to multiply that by the Unit Price, which has been increased or shocked for the Price Change. For the cost part, we’re going to increase the quantity again by the demand. But instead of Price Change, we’re going to incorporate the Cost Change.
With this one formula, we can work out what our scenario profits are by incorporating all of these individual what if parameters in Power BI. Understanding SUMX and iterators in general is really crucial. You have to remember that formulas with an X on the end iterate through every single row in the table. So for every single individual transaction or sale we have made, we can then place these shocks on top of them.
Incorporating Scenarios Inside Logic
Remember that we have incorporated multiple scenarios and multiple measures. These measures change based on our scenario selection, and we can now incorporate these scenarios inside our logic. I have individually calculated the Best Case scenario, Ok Case scenario, and Worse Case scenario.
If we go to those formulas, you will see all I’ve done is to utilize the Scenario Profits and use CALCULATE to break out what the Best Case result would be. I have also done this for the Ok Case result and the Worse Case result.
Using this formula will deliver us this chart which allows us to compare with our general forecasted profits.
Working Out The Cumulative Results
On top of what I’ve already done, I wanted to see how things are cumulatively, and see how things accumulate over time. I used these formulas and branched out again using the cumulative total pattern. You can see here that I have incorporated or branched out the previous measure inside the same pattern every single time, which is giving us individual scenario cumulatively.
The cool thing about this chart is that no matter what time frame we select, it automatically adjusts. If we only wanted to see what was the dollar impact cumulatively over a three-month period, we can see that cumulatively. For example, we can see that the Best Case is going to deliver us $8.4 million versus the Worst Case which is $4 million.
***** Related Links *****
How To Start Using ‘What If’ Parameters Inside Power BI
Power BI ‘What-If’ Parameter Feature
Scenario Analysis Techniques Using Multiple ‘What If’ Parameters
Conclusion
In this specific example I showed you not only DAX measures but also how you can create a table of information that could classify certain scenarios. You might want to classify a Best Case scenario, an Ok scenario, or a Worst Case scenario. Within these scenarios, you might have multiple things that change around your pricing or your demand, or your costs.
By doing both of these things very well, you can in a dynamic way showcase results or performance either historically or into the future, and be able to evaluate very quickly and automatically what your results might be.
There is so much to learn with this example, and heaps of techniques to go into. Hopefully you can get out of it the exponential ways that you can change the environment in which your analysis and your results are calculated.
Never before have we really had a tool that enables us so easily and effectively create this type of advanced analysis. Good luck with implementing all of these things in your own models. Any comments, certainly let me know down below.
If you want to dive into scenario analysis in greater detail, certainly check out my Scenario Analysis Deep Dive course. So much to learn about this incredible analytical technique inside of Power BI.
