Question

How can I dynamically create what-if parameters that allow for real-time scenario planning?
I need a way to dynamically generate what-if parameters in Power BI that let users perform real-time scenario analysis. The solution should allow users to input different values and instantly see how changes affect key metrics. What is the best approach to implementing dynamic what-if analysis using DAX and Power BI’s parameter features?

Answer 1

To dynamically create what-if parameters in Power BI for real-time scenario planning, follow these steps:

1. Use Power BI’s Built-in What-If Parameter

• Go to Modeling > New Parameter > What-if Parameter.

• Define a range, increment, and default value. For example, to analyze price changes:

  • Name: Price Adjustment

  • Minimum: -20%, Maximum: 20%, Increment: 1%, Default: 0%

• This creates:

  • A disconnected table with parameter values.

  • A measure to reference the selected parameter:

SelectedAdjustment = SELECTEDVALUE('Price Adjustment'[Price Adjustment], 0)

2. Apply What-If Parameter in Measures

• Create a measure to apply the selected parameter to key metrics. Example for adjusting sales revenue:

Adjusted Sales = 
    SUM(Sales[Revenue]) * (1 + [SelectedAdjustment])

• This dynamically updates when users select a different parameter value in a slicer.

3. Allow User Input for More Flexibility

• If users need custom inputs beyond pre-defined values:

  • Use a numeric field in an Excel/SQL/PowerApps data source where users can input values.

  • Create a measure using MAX(UserInput[Value]) to capture the latest user entry.

4. Enhance Scenario Planning with Multiple Parameters

• Create multiple what-if parameters (e.g., price, discount, cost) and combine them in a measure:

Scenario Sales =
    SUM(Sales[Revenue]) * (1 + [Price Adjustment]) * (1 - [Discount Adjustment])

Use slicers for real-time adjustments across different dimensions.