Create what-if parameters
You can use what-if parameters to run scenarios and scenario-type analysis on your data. What-if parameters are powerful additions to your Power BI semantic models and reports because they enable you to look at historical data to analyze potential outcomes should a different scenario occur. Additionally, what-if parameters can help you to predict or forecast what could happen in the future.
You can use what-if parameters in multiple situations. For instance, you can determine the effect of increased sales to deeper discounts, or let sales consultants see their compensation should they meet certain sales goals or percentages.
Consider a scenario where you want to find out how much growth the Sales team needs to make in order to earn 2 million dollars gross sales each month.
Create a what-if parameter
To create a what-if parameter, follow these steps:
On the Modeling ribbon tab, select New Parameter.
In the What-if parameter window, configure the new parameter.
For this example, change the parameter name to Sales Forecast Percentage.
Set the Data type to Fixed decimal number because you're using currency in your forecast.
Set the Minimum value to 1, the Maximum value to 1.50, and the Increment value to 0.05, which is how much the parameter adjusts with report interactions.
Set the Default value to 1.00.
Leave the Add slicer to this page checkbox selected so that Power BI automatically adds a slicer with your what-if parameter to the current report page.
Note
For decimal numbers, make sure that you precede the value with a zero (as in 0.50 versus .50).
The new slicer visual appears on the current report page. You can move the slider to see the numbers increase according to the settings that you applied. You should also see a new field for the Sales Forecast Percentage table in the Data pane, and when you expand that field, the what-if parameter should be selected.
Similarly, you should see the measure that was also created. You can use this measure to visualize the current value of the what-if parameter.
After you create a what-if parameter, the parameter and the measure are parts of your data model. Therefore, they're available to the report and can be used on other report pages. Additionally, because the parameter and measure are part of the model, you can delete the slicer from the report page. If you want it back, you can drag the what-if parameter from the Data pane onto the canvas and then change the visual type to a slicer.
Use a what-if parameter
You need to create a new measure whose value adjusts with the slider to use the new parameter. You can create complex and unique measures that let your report users visualize the variable of your what-if parameter. However, to keep this example simple, the new measure is the total sales amount, with the forecast percentage applied, as show in the following measure expression.
Gross Sales Forecast =
[Gross Sales] * [Sales Forecast Percentage Value]
Next, you create a clustered column chart with the MonthName field on the axis and the Gross Sales and Gross Sales Forecast measures as its values.
Initially, the bars are similar; however, as you move the slider, notice that the Gross Sales Forecast column reflects the sales forecast percentage amount.
Add a constant line
To enhance the visual, you can add a constant line so that you can clearly see how the organization is performing against a particular threshold or target. In this example, we add a constant line with 2 million dollars as the threshold value. Then use the slider to find out by what percentage increase sales must grow to reach the threshold. In the following image, the gross sales need to increase by 1.40 percent to reach the two million dollar threshold.
