What-if parameters

Power BI Desktop provides a user interface for more easily creating What-if parameters than the custom slicer parameter demonstrated earlier in this chapter. This option is currently limited to numeric parameter values but automatically creates a single column table and a DAX measure that retrieves the input value.

In the following example, two What-if parameters are used to calculate alternative unit price and unit cost values thereby driving a hypothetical product margin % measure:

What-if parameters applied in report visuals

By adjusting the two slider bars, a user is able to quickly model an alternative gross product margin % scenario, as illustrated by the dotted line in the line chart visual. The slider bar for modifying a single value is unique to slicers for What-if parameter columns.

To create a What-if parameter, click the New Parameter icon on the Modeling tab in Report View to launch the following dialog:

Creating a What-if parameter

Based on the minimum, maximum, and increment input values specified, a new table with a single column of values will be created within the Power BI dataset. For the Unit Price Growth parameter, this column has 20 rows from 0 to .19 with each value representing a full percentage point (for example, 0% to 19%). Additionally, a new DAX measure is created automatically to retrieve the user selection, per the following expressions:

Internet Sales Unit Price Growth Value = 
SELECTEDVALUE('Internet Sales Unit Price Growth'[Internet Sales Unit Price Growth], 0)

Internet Sales Product Unit Cost Growth Value = 
SELECTEDVALUE('Internet Sales Product Unit Cost Growth'[Internet Sales Product Unit Cost Growth], 0)

With the second argument to both functions set to 0, both growth values will return zero if a selection hasn't been made or if multiple values have been selected. The same SELECTEDVALUE() function, which was added to the DAX language in 2017, was also used in the custom slicer parameters example earlier in this chapter.

The only remaining step is to create one or more measures that reference the parameter values in their calculation logic. In this example, the Unit Price and Unit Cost growth parameters are applied to gross sales and product cost scenario measures, respectively. These two scenario measures are then used to compute a product margin scenario measure and a product margin % scenario measure, per the following expressions:

Internet Gross Sales Scenario = SUMX('Internet Sales','Internet Sales'[Order Quantity]*
  ('Internet Sales'[Unit Price]*(1 + [Internet Sales Unit Price Growth Value])))

Internet Sales Product Cost Scenario = SUMX('Internet Sales','Internet Sales'[Order Quantity] * 
  ('Internet Sales'[Product Standard Cost] * (1 + [Internet Sales Product Unit Cost Growth Value]))) 

Internet Gross Product Margin Scenario = 
[Internet Gross Sales Scenario] - [Internet Sales Product Cost Scenario]

Internet Gross Product Margin % Scenario = 
DIVIDE([Internet Gross Product Margin Scenario],[Internet Gross Sales Scenario])

Although it's possible and sometimes necessary to create parameter columns and measures manually, the What-if parameter feature in Power BI Desktop can simplify this process for many modeling scenarios. Additionally, the slider bar slicer exclusive to the What-if parameter columns is the most user-friendly option for selecting parameter values.

To change the range of values available to the parameter, select the parameter column in the Fields list and modify the min, max, or increment arguments to the GENERATESERIES() function. Based on the user interface selections from the Unit Price Growth parameter, Power BI built the following function: GENERATESERIES(0,20,.01).

Table of Contents for What-if parameters

Create new playlist

Sign In

Sign Up

Table of Contents for
What-if parameters