What if Analysis in Power BI (Multi-layered Scenario Building)

[Anonymous]

WHAT-IF SCENARIO ANALYSIS WITH POWER BI

Power BI facilitates a fantastic scenario analysis technique which is used to determine how an actual or projected measure is affected by changes in the assumptions upon which that measure is calculated. This business technique is called as Sensitivity Analysis as well. What-if analysis is often used to compare different scenarios and their potential outcomes based on changing conditions.

For using this feature, we can create What-If parameter and interact with the variable as a slicer. Based on the input in the slicer, we can visualize and quantify changes in report data.

This capability has been released as part of the August update to Power BI Desktop 

Business Scenario

I am considering one common business scenario of Revenue / Cost / Margin calculation. I have got the revenues / cost for four consecutive years in an excel. Now I want to come up with one chart to show Revenue / Cost proportion along with Margin %. At the same time, I would like to provide a flexibility to add some discount on Pricing or increase in cost and that should refresh my chart data dynamically.

1. First, I am loading the excel which includes the Revenue / Cost data as below.

Fig 1 

Creating What-if Parameter

1. Next, I am creating What if Discount parameter from the Modeling ribbon, by clicking the New Parameter button.

Fig 2 

1. This opens the What-if parameter dialogue window Fig 3. I am giving a name to the parameter and setting the data type, Minimum, Maximum, and Increment values. If required, a Default value can be set as well. The Add slicer to this page check box will automatically create a special kind of slicer that is used to select a single value for your what-if analysis.

Fig 3 

1. Once I click OK, a calculated table is created based on the input values in Fig 3. The table is created with no relationships to any other table. Also, a calculated measure is created in the new calculated table. This is the measure we need to reference in order to make our what-if calculation dynamic.

Fig 4 

1. Now with the loaded excel data, I am going to create a Pie chart to show Total Revenue / Total Cost (for the 4 years starting 2019 through 2022). Initially, Discount % is set to 0 in the slider. With that condition, I am getting the Total Revenue / Gross Margin % / Net Margin % shown in Fig 5.

Fig 5 

Using What-if Parameter                                                                                                                      

1. Now I want to use the Discount parameter and see that how my Revenue / Margin data are changing based on that.
2. To do that, few new measures need to be created whose values are supposed to be adjusted with the Discount slider. Fig 6 shows, New Measure option is there in Modeling tab.

Fig 6 

1. In my excel, I am getting the revenues for four years Y1, Y2, Y3, Y4 separately. Same way I am getting the cost individually for each of the years. Refer Fig 7

Fig 7 

1. So, to get the Total Revenue, I have to sum up the revenues for Y1, Y2, Y3 and Y4. First, I am creating separate measures to calculate Y3 Discounted Revenue / Y4 Discounted Revenue, Y1 Total Revenue Discounted, Y2 Total Revenue Discounted by applying the Discount measure.
2. At last, one more measure is created by adding all the discounted Revenues mentioned above. Fig 8 shows the same.

Fig 8 

1. After saving the changes, once I enter 25% discount in the slider, Total Revenue, Gross Margin % and Net Margin % got adjusted as below Fig 9.

Fig 9 

Multi-layered Scenario Analysis

1. Till now I have used only one What if parameter to give discount to the Total Revenue but there was no effect on Cost for that. Now I am going to derive multi-layered analysis by using Discount on Pricing and Increase on Cost at the same time to impact Margin %.
2. I will create a new What if parameter Cost Increase with the same way as earlier.

Fig 10 

1. This Cost Increase parameter should increase the Cost by the given % in the Cost Increase slider. For that, I will create few new measures to calculate the discounted cost for each of the year and at last calculating the Total Cost by adding the cost for individual four years.

Fig 11 

1. After saving the changes, if I add Cost Increase as 10% (with 0% discount on Pricing), then Total Revenue should be same as Fig 5 but Gross Margin % and Net Margin % should come down as cost has gone high. That is working correctly. Fig 12

Fig 12 

1. But till now I have seen both the What if sliders are affecting their relevant parameters individually and the data is changing.
2. Now I will try to add Discount % on Pricing and Increase % on Cost and will see how the data on the chart changes with the what if parameters together. Shown in Fig 13. This is a pure example of multi-layered scenario analysis in Power BI. By conducting a what-if analysis with multiple assumptions, we can explore various possible scenarios and make better decisions as a result. 

Fig13 

[Chris_Greer]

Is it possible to save the paramter values back to a table somehow if I find somethign I want to use agian later?

[Anonymous]

Very Informative and helpful content to start with sample business use case for learning power BI report.

[v-easonf-msft]

Hi ,  @Anonymous 

Thank you for your sharing.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Anonymous , drop a message to @JCM_iTalent  and try to put this as a blog on power bi community