The what-if feature within Power-BI is essentially a scenario analysis technique that can provide some interesting insights around different scenarios that may occur based on changes in the data. It allows us to view data holistically, while observing the effects of a value change on individual measures.
Why would we need a what-if parameter?
A what-if parameter allows for data to change dynamically depending on the value of the parameter. It’s useful for different projected scenarios, sensitivity analysis and viewing the effects of an increase or decrease.
How can we create one?
Depending on the dataset and needs there are a few things to consider. Firstly, we should consider the parameter value range. So, if we click the what if parameter button the following box will appear, which allows us to set a range of potential values. For the purpose of this example, we will set the range from 0 to 100 and ensure it increases by increments of 1.
This will automatically create a single column table with a list of values that span from the minimum and maximum values that have been decided. We could have set the minimum as a negative figure here if we wanted to observe the effects of a percentage decrease, however for this scenario we will stick to observing the effects of a percentage increase.
In addition to the table being created, this also adds a slicer to the page and measures can be created based on the parameter value so that the results change depending on the value of this slicer. Essentially, this will demonstrate how the data changes depending on the scenario.
So far, we have got a slicer and the next step is to start creating some measures that will automatically adjust the value as it will be using the parameter value. We will start off by creating a very basic calculation which uses the parameter value to increase the units sold by a percentage value. As we adjust the parameter value, the increase in the units sold bar will reflect the value. Both the parameter and measure are now part of the model and can be used on other pages too.
As can be seen from the image above, the original value is represented by the units sold bar on the left and the increase in units sold is dependent on the parameter value and adjusts dynamically depending on the value.
We can also add an additional measure which calculates the new total profit based on the percentage increase in units sold and unit price, as can be seen above. This changes dynamically depending on the value that we decide to set the percentage increase for.
It’s a useful feature that can aid analysis, help in making quick, informed decisions and provide insights under various scenarios. In addition to this:
– The feature removes a lot of the manual work involved
– It facilitates predictive analytics to some extent.