Power BI What If Parameters

BY: David Rohlfs

Introduction

The most important part of reporting data is making sure the data is correct. And because of this a lot of data reporting is centered on past data, but that is not all of what reporting data is about. There is a lot of time and money spent on forecasting, and PBI is no exception to that.

Normally parameters are used to section off data, but they can also be used to forecast data in what is called a “What-If Parameter”. What-if parameters allow for an end-user to see forecasted data that is dynamic with a slicer.

In this blog we will go over when to use what-if parameters, common problems associated with them, and a walkthrough demonstrating building a what-if parameter.

 

When to use it

Although what-if parameters purpose is to help forecast what can happen with data, they are not a true representation of a forecast. Because of this a what-if parameter is best used when you want to compare data for possible scenarios, boosts or declines, or just to mess around with.

Like a basic parameter, what-if parameters are changing your semantic model to filter or adjust for a value or point that you choose. But unlike a basic parameter, a what-if parameter is adjusting for possible data instead of historical data.

One example is if you had a new sales team coming on and expect a boost in sales for the preceding months. To see how your data may change for the future months, you decide to create a what-if parameter. Using a sales forecasting parameter, you can estimate the increase in sales for the upcoming months.

 

A few reasons you may use a what-if parameter is:

• When you want to decide between multiple choices.
• See how an increase in a variable could affect a result.
• To compare multiple variables against a result.
• To add another level of interactivity in a report.
• See how a variable can change organizational factors.

 

It is good practice to make a separate page in your report from historical data and forecast data. Doing this will help reduce confusion in your report between real data and potential data.

 

Common Problems

A what-if parameter is built by a report builder, therefore there will usually be a higher level of variance from the parameter’s predictions to a true forecast. Because this can be a potential problem, the report builder should make it known that this function should be used for a rough estimate, and not to determine highly accurate forecast reports.

 

Walkthrough

In this walkthrough we are going to go through building a what-if parameter for a sales team. To do this we will be using past sales and multiplying them against a value that can be altered by the end-user to produce dynamic results.

 

First, we are going to create our new parameter. To do that click on the modeling tab, new parameter, and numeric range.

 

Next, we can enter our data. Notice that we are not choosing a field to base this on, that means that we are going to be using this parameter in a measure to alter our data. For this scenario we are changing our name to “Sales Forecast Multiplier”, our Data Type to Decimal Number, our Minimum to 0.05, or Maximum to 2.5, our Interval to 0.05, and our Default to 1. These values will change based on your preferences and goals.

 

If you get an error at this point, make sure that you have included a 0 before your decimal number. If you don’t do this your data won’t be validated in Power BI.

There is an option to add this slicer to your page before you click create. This slicer is how the end-user will be able to change your what-if scenario later, so you will usually want to have this box checked.

 

Once you click create you will see two things happen, a slicer will be added to your page, and a new field will appear with the parameter and a measure that can be used to demonstrate the parameter value in a card or in a measure.

 

Now that you have created this what-if parameter, we can begin to use it in a DAX equation. This will be done in a measure because it will normally work better than with a calculated column and help report performance. For this scenario I am multiplying my sales by the parameter; this will let me play with possible forecasting amounts.

 

After you build your equation, you can start building your page, visuals, and cards. This is an idea of what your forecast page could look like.

 

Conclusion

Although a lot of reporting is based on historical data, using a what-if parameter can help to show options to an end-user. This is important if you want to demonstrate how different variables can affect results and it creates a lot of opportunities for reporting a semantic model.

Comments