What if Parameter in Power BI

In this Power BI article, we will learn what is What if Parameter in Power BI is and how to add the What if Parameter to the Power BI report.

What if Parameter Power BI

• We can easily analyze data using the what-if parameter in Power Bi. It displays dynamically the value based on the condition applied for different scenarios.
• For example, we can easily check what if sales increased by 10% using the what-if parameter in Power Bi.

Add What if Parameter in Power BI

Let us see how we can add or create a What-if parameter in the Power Bi report.

In this example, we are going to use the sales table data, to add the what-if parameter slicer visuals in Power Bi.

• Open the Power Bi desktop, and load the data into it using the get data option. Once the data has been loaded, select the clustered column chart and drag and drop the customer location and sales field values in it as below:

• Now Click the Modeling tab in the top ribbon and select the What IF parameter as highlighted below:

• A What if parameter pop window appears, In the Name section we can enter the parameter name, here the parameter name is Sales%.
• In the Data type, we can select the data type as a whole number, decimal number, and fixed decimal number.
• We can also set Minimum value, Maximum value, Increment, and Default value, and to save the What-if parameter clicks on the ok button below:

• Under the Fields pane, we can see that the Parameter has been created as a New Table

• Once the parameter has been created. It automatically creates two measure values for the generated series and for the selected value

For generated series:

Sales % = GENERATESERIES(1, 2, 0.01)

For Selected Value:

Sales % Value = SELECTEDVALUE('Sales %'[Sales %], 1)

• Now we can apply the parameter to calculate the percentage of the total sales, create a new measure and apply the below-mentioned formula:

Total Sales % = Sales_Table[totalsales] * 'Sales %'[Sales % Value] 

Where,

1. Total Sales % = New measure name
2. Sales_Table = Table Name
3. Sales %'[Sales % Value] = Parameter Value

• Now simply drag and drop the created measure value in the clustered column chart. The screenshot below displays the sales and total sales value as the same.

When we change or slide the Parameter slicer value, the clustered column chart displays the total sales value according to the selection.

This is how we can add or create a What-if parameter in the Power Bi report.

Also, read Power BI what if Parameter Percentage

Use a what-if parameter for percentages, date ranges, and Top N filters

In current Power BI Desktop, the old What if parameter button is now Modeling > New parameter > Numeric range. The dialog still asks for a name, data type, minimum, maximum, and increment, and you can tick Add slicer to this page so the slider shows up right away.

Power BI then builds a parameter table with GENERATESERIES and a SELECTEDVALUE measure ending in “Value”. Every pattern below just plugs that Value measure into another measure.

What-if parameter as a percentage

For a true percentage slider, pick Decimal number, set Minimum to 0, Maximum to 0.50, and Increment to 0.05. Type the leading zero (0.50, not .50), or Power BI won’t validate the value and the Create button stays grayed out.

Next, select the parameter column in the Data pane and set its format to Percentage, so the slicer reads 5%, 10%, and so on instead of 0.05. Then write a measure that applies it:

Sales after Discount =
SUM ( Sales[SalesAmount] )
    - SUM ( Sales[SalesAmount] ) * [Discount percentage Value]

Put SalesAmount and Sales after Discount side by side in a column chart. When you drag the slider to 30%, the second column drops by 30% while the first one stays put, which makes the scenario easy to explain.

What-if parameter for a rolling date range

To let users pick “last N years” (or months), create a Whole number parameter named Dynamic Year from 1 to 10 with an increment of 1. Then pass its Value measure, made negative, to DATESINPERIOD so the window runs backward from the latest date:

Quantity Last N Years =
CALCULATE (
    SUM ( Sales[Quantity] ),
    DATESINPERIOD (
        'Date'[Date],
        MAX ( 'Date'[Date] ),
        - [Dynamic Year Value],
        YEAR
    )
)

Drop the measure on a card. With the slider at 3 and data through 12/31/2026, the card totals the quantity for 01/01/2024 through 12/31/2026. Swap YEAR for MONTH or QUARTER if you need a shorter window.

Use a marked date table for the first argument when you can. DATESINPERIOD only returns dates that exist in that column, so a sales date column with gaps can quietly shrink your window.

What-if parameter as a Top N filter

For a Top N slider, create a Whole number parameter called Top N from 1 to 10. Rank customers with RANKX, then return sales only when the rank falls inside the slider value:

Customer Rank =
RANKX ( ALL ( Sales[Customer Name] ), [Total Sales], , DESC )

Top N Sales =
IF ( [Customer Rank] <= [Top N Value], [Total Sales] )

Add Customer Name and Top N Sales to a column chart. Customers outside the Top N return blank, so the chart hides them. Move the slider to 4 and you’ll see just the four best customers.

One limit to remember: a numeric range parameter holds at most 1,000 unique values. If your min, max, and increment produce more, Power BI samples them evenly, so pick a coarser increment.

Power BI what if the parameter in a calculated column

• No, the Calculated column doesn’t support the What if Parameter in Power Bi. we cannot filter the values dynamically in the calculated column using the What if Parameter.
• Because Power Bi What if parameter supports and filters the values dynamically only on the Power Bi DAX measures. So we can replace the Calculated column with the Power Bi measures and use the What if Parameter.

What if Parameter Power Bi Text

• No, it is not possible to input the string data type into the Power Bi text using the What if Parameter.
• In the Power BI What-if parameter, we can select the data type as a whole number, decimal number, and fixed decimal number. and not the string data type to input the string value.

This Power BI article demonstrated what is What if Parameter is and how to add the What if Parameter to the Power BI report.

You may like the following Power BI tutorials:

• What if parameter Power BI Date
• What if Parameter in Power BI filter
• How to sort slicer by measure in Power BI