Power BI – What If Parameter

A What If Parameter allows for the visualization and analysis of the effects of a change in a variable.

The example I use in this post is a salary cap projection. A fictitious sports team has historical data tracking a rising salary cap and total roster spend over the past seven years. There is also a projection of both variables over the next three years.

But what if these projections are wrong? Incorporating a what if parameter into the report allow for easy visualization of different projection scenarios.

Building the Parameter

The first step towards using a what if parameter is to build one. I navigated to the Modeling tab on the ribbon and selected New Parameter from the What If section.

This brings up the pane to create the parameter. There are several aspects to define:

• the name of the parameter
• the data type
• the minimum and maximum values
• the increments between these two values
• the default value of the parameter

The parameter I wanted to create was the variance in the salary cap projection. I set the data type as a decimal number, to capture the percent difference from the projection.

The minimum value was set to 0.90, in case the salary cap came in under the projection.

The maximum value was set to 1.25, or 25% greater than the projection.

Visualizing the Parameter

Two items appeared in the Fields pane after creating the parameter.

1. Parameter Series (Salary Cap Increase)
2. Parameter Measure (Salary Cap Increase Value)

The parameter series created the possible values to populate the measure. It is generated with the following DAX code:

= GENERATESERIES(0.90, 1.25, 0.05)

The series is visualized as a slicer on the report canvas to change the parameter and see the results.

The parameter measure is what can be incorporated into visuals, or referenced in other measures. I have included the measure with a card visual.

Building on the Parameter Measure

At this point, I had built a measure that changes with the slicer in the report. To leverage this measure in my analysis, I needed to build a new measure incorporating the parameter measure.

I created a new DAX measure called Adjusted Salary Cap. This dynamically changes the value of the salary cap projection based on the value of the what if parameter.

I built the measure with the following DAX code (after a lot of help from this YouTube video):

= SUMX(Data, IF(RELATED(Calendar[Year]) > 2017, Data[Salary Cap] * 'Salary Cap Increase'[Salary Cap Increase Value], Data[Salary Cap]))

This measure multiplies the existing projection by the change in the parameter for all future years. All previous years will return the historical salary cap value.

Visualizing New Measure

Now that I had a DAX measure that includes the dynamic what if parameter, I visualized it.

The existing line chart plotted the salary cap in light blue and the total roster spend in darker blue over a ten year period.

The Salary Cap and Adjusted Salary Cap lines had the same value from the years 2010 to 2017. The lines differed in all future years, depending on the value of the parameter.

I set the Salary Cap Increase parameter to 0.90 with the slicer, the minimum value. This changed the Adjusted Salary Cap measure that I had plotted on the line chart to below the existing projection.

When I changed the slicer to 1.15, the pink Adjusted Salary Cap measure shifts above the blue projected Salary Cap line. If this projection holds true, there will be more salary cap room for the team to spend on the roster.

That’s my take on setting up a what if parameter in Power BI! Thank you to everyone who voted in my Twitter poll last week that determined this topic.

Make sure to follow me on Twitter @FthrsAnalytics to stay up to date with my website and other exciting news!