Mastering Power BI What-If Parameters and Scenario Analysis: Building Dynamic Forecasting and Sensitivity Models

Introduction

Your sales director walks into the Monday morning meeting and asks, "What happens to our margin if raw material costs increase by 15%?" Your CFO follows up with, "And if we simultaneously raise prices by 8%, do we still hit our Q3 targets?" You have a beautiful Power BI dashboard open on the screen, but right now it only tells them what did happen — not what could happen. You're stuck opening Excel, building a quick model, and presenting something that looks disconnected from your carefully designed BI environment.

This is exactly the gap that What-If Parameters and scenario analysis fill. When implemented properly, they transform Power BI from a rearview mirror into a forward-looking decision engine. Instead of static reports, you build interactive models where stakeholders can move sliders, adjust assumptions, and immediately see the financial or operational impact — all within the same trusted data environment.

By the end of this lesson, you'll know how to build production-grade what-if and sensitivity models that your business stakeholders will actually use. We'll go beyond the basic slider setup and build a multi-variable scenario engine with realistic business logic.

What you'll learn:

• How What-If Parameters work under the hood in DAX, and why that matters for building more complex models
• How to construct multi-variable sensitivity analyses that handle interdependent assumptions
• How to build named scenario switching (Best Case / Base Case / Worst Case) alongside continuous sliders
• How to manage parameter interactions so your model doesn't produce nonsensical results
• Performance considerations and when to use disconnected tables vs. native What-If Parameters

---

Prerequisites

You should already be comfortable with:

• Writing intermediate DAX measures (CALCULATE, FILTER, SUMX, RELATED)
• Creating relationships between tables in Power BI Desktop
• Basic DAX table functions (ALL, VALUES, SELECTEDVALUE)
• The concept of measure branching (using measures inside other measures)

If calculated tables and disconnected slicers feel unfamiliar, spend thirty minutes with those concepts first — we'll use both extensively here.

---

Understanding What-If Parameters Under the Hood

Before you drag a slider around, you need to understand what Power BI is actually creating when you add a What-If Parameter. This knowledge is the difference between someone who uses the feature and someone who can actually build with it.

When you add a What-If Parameter through the Modeling ribbon, Power BI generates two things automatically:

1. A calculated table, which is a single-column table populated by the GENERATESERIES() function. For example, if you create a parameter called "Price Increase %" ranging from 0% to 30% in 1% increments, Power BI creates this:

Price Increase % = GENERATESERIES(0, 0.30, 0.01)

This produces a table with one column named [Price Increase %] containing 31 rows: 0.00, 0.01, 0.02, … 0.30.

2. A DAX measure that reads the current slicer selection from that table:

Price Increase % Value = 
SELECTEDVALUE('Price Increase %'[Price Increase %], 0)

The SELECTEDVALUE() function returns the single selected value from the parameter table when the slicer is set to one value, and returns the default (0 in this case) when nothing is selected or multiple values are selected.

Here's the critical insight: these parameter tables have no relationships to anything else in your model. They exist in isolation. The only way they influence your calculations is through DAX measures that explicitly reference them. This is actually a feature, not a bug — it means your parameter logic never interferes with your filter context, and you can use these values in any measure anywhere without side effects.

Tip: Always check the auto-generated parameter measure after Power BI creates it. The default value in SELECTEDVALUE() will be whatever you set as the default in the wizard. Make sure that default makes business sense — a discount rate defaulting to 0% when your base case is 5% will confuse stakeholders.

---

Building Your First Multi-Variable Sensitivity Model

Let's build something realistic. We're working with a manufacturing company that sells three product lines. The model needs to let users explore:

• A price adjustment (ranging from -10% to +20%)
• A volume growth rate (ranging from -15% to +25%)
• A cost inflation rate (ranging from 0% to +20%)

Our source data has a Sales table with columns: [Date], [Product], [Units Sold], [Unit Price], [Unit Cost], and [Revenue].

Step 1: Create the Parameter Tables

Go to Modeling → New Parameter (in newer versions of Power BI Desktop this is under the Modeling tab as "New parameter" under the What-if section). Create three parameters:

Price Adjustment %

• Minimum: -0.10, Maximum: 0.20, Increment: 0.01, Default: 0

Volume Growth %

• Minimum: -0.15, Maximum: 0.25, Increment: 0.01, Default: 0

Cost Inflation %

• Minimum: 0, Maximum: 0.20, Increment: 0.01, Default: 0

Power BI will create three calculated tables and three auto-generated measures. Rename the generated measures to be explicit — instead of Price Adjustment % Value, use Param_PriceAdj, Param_VolumeGrowth, and Param_CostInflation. This naming convention makes it obvious in the measure list which measures are parameter readers vs. business calculations.

Step 2: Build the Base Measures

Before touching the parameter measures, establish your historical baselines. These measures calculate actuals from real data:

Base Revenue = 
SUMX(
    Sales,
    Sales[Units Sold] * Sales[Unit Price]
)

Base Units = 
SUM(Sales[Units Sold])

Base Cost = 
SUMX(
    Sales,
    Sales[Units Sold] * Sales[Unit Cost]
)

Base Gross Margin = 
[Base Revenue] - [Base Cost]

Base Gross Margin % = 
DIVIDE([Base Gross Margin], [Base Revenue], 0)

Test these against your raw data before proceeding. Nothing breaks a sensitivity model faster than base numbers that don't match what finance already knows.

Step 3: Build the Projected Measures

Now layer the parameters onto the base calculations. Notice how each measure reads the parameter values and applies them independently:

Projected Revenue = 
VAR PriceAdj = [Param_PriceAdj]
VAR VolumeGrowth = [Param_VolumeGrowth]
RETURN
SUMX(
    Sales,
    Sales[Units Sold] * (1 + VolumeGrowth) * Sales[Unit Price] * (1 + PriceAdj)
)

Projected Cost = 
VAR CostInflation = [Param_CostInflation]
VAR VolumeGrowth = [Param_VolumeGrowth]
RETURN
SUMX(
    Sales,
    Sales[Units Sold] * (1 + VolumeGrowth) * Sales[Unit Cost] * (1 + CostInflation)
)

Projected Gross Margin = 
[Projected Revenue] - [Projected Cost]

Projected Gross Margin % = 
DIVIDE([Projected Margin], [Projected Revenue], 0)

Revenue Delta = 
[Projected Revenue] - [Base Revenue]

Revenue Delta % = 
DIVIDE([Revenue Delta], [Base Revenue], 0)

Warning: A common mistake here is referencing [Param_VolumeGrowth] multiple times in the same SUMX without caching it in a VAR. Each reference recalculates the SELECTEDVALUE call. In large models, always cache parameter values in VAR statements at the top of your measure. It's cleaner and safer.

---

Building Named Scenario Switching

Continuous sliders are powerful, but business stakeholders often think in scenarios: "Show me Best Case, Base Case, and Worst Case." You can combine both approaches in a single model — let users pick a named scenario from a dropdown, and also let them fine-tune with sliders.

Create the Scenario Table

Don't use the What-If Parameter wizard for this one. Instead, create a disconnected table manually. In Power BI Desktop, go to Modeling → New Table:

Scenarios = 
DATATABLE(
    "Scenario", STRING,
    "ScenarioOrder", INTEGER,
    "PriceAdj", DOUBLE,
    "VolumeGrowth", DOUBLE,
    "CostInflation", DOUBLE,
    {
        {"Worst Case", 1, -0.05, -0.10, 0.15},
        {"Base Case",  2,  0.00,  0.03, 0.05},
        {"Best Case",  3,  0.08,  0.12, 0.02}
    }
)

This is a proper disconnected table — no relationships, just a lookup for assumptions. Sort the Scenario column by ScenarioOrder so your slicer displays in logical order (Worst, Base, Best) rather than alphabetical.

Create the Scenario-Aware Measures

Now you need measures that can read either the slicer-selected scenario OR fall back to the manual slider values. The logic should be: if a scenario is selected, use those assumptions; if no scenario is selected (or a custom scenario), use the slider values.

Active Price Adj = 
VAR SelectedScenario = SELECTEDVALUE(Scenarios[Scenario], "Custom")
VAR ScenarioPriceAdj = 
    CALCULATE(
        SELECTEDVALUE(Scenarios[PriceAdj], 0),
        Scenarios[Scenario] = SelectedScenario
    )
RETURN
IF(
    SelectedScenario = "Custom",
    [Param_PriceAdj],
    ScenarioPriceAdj
)

Active Volume Growth = 
VAR SelectedScenario = SELECTEDVALUE(Scenarios[Scenario], "Custom")
VAR ScenarioVolumeGrowth = 
    CALCULATE(
        SELECTEDVALUE(Scenarios[VolumeGrowth], 0),
        Scenarios[Scenario] = SelectedScenario
    )
RETURN
IF(
    SelectedScenario = "Custom",
    [Param_VolumeGrowth],
    ScenarioVolumeGrowth
)

Active Cost Inflation = 
VAR SelectedScenario = SELECTEDVALUE(Scenarios[Scenario], "Custom")
VAR ScenarioCostInflation = 
    CALCULATE(
        SELECTEDVALUE(Scenarios[CostInflation], 0),
        Scenarios[Scenario] = SelectedScenario
    )
RETURN
IF(
    SelectedScenario = "Custom",
    [Param_CostInflation],
    ScenarioCostInflation
)

Now refactor your projected measures to use the Active parameter measures instead of the raw Param_ measures:

Projected Revenue v2 = 
VAR PriceAdj = [Active Price Adj]
VAR VolumeGrowth = [Active Volume Growth]
RETURN
SUMX(
    Sales,
    Sales[Units Sold] * (1 + VolumeGrowth) * Sales[Unit Price] * (1 + PriceAdj)
)

On your report canvas, add a slicer for Scenarios[Scenario] and configure it as a single-select dropdown. When a user selects "Best Case," all three parameters auto-populate from the scenario table. When they select nothing (or you add a "Custom" option), the sliders take over.

Tip: Add a "Custom" row to your Scenarios table so users can explicitly choose slider control without leaving the slicer empty. An empty slicer selection is ambiguous; an explicit "Custom" option is clear.

---

Building a Sensitivity Table (Tornado Analysis)

Sliders are great for exploration, but decision-makers often want to see which variable matters most. A sensitivity table — sometimes called a tornado chart — answers this by showing the projected outcome across a range of values for each variable independently.

The challenge in Power BI is that a standard visual shows one selected value per parameter. To build a sensitivity range, you need a different approach: use a separate analysis table that holds the test values.

Create the Sensitivity Driver Table

SensitivityDrivers = 
DATATABLE(
    "Driver", STRING,
    "LowValue", DOUBLE,
    "HighValue", DOUBLE,
    {
        {"Price Adjustment",  -0.10, 0.20},
        {"Volume Growth",     -0.15, 0.25},
        {"Cost Inflation",     0.00, 0.20}
    }
)

Create the Sensitivity Measures

These measures calculate what happens to projected gross margin when each driver is at its low and high value, holding the other drivers at base case (zero in this example, but you could point them at your Base Case scenario):

Sensitivity Low Margin = 
VAR CurrentDriver = SELECTEDVALUE(SensitivityDrivers[Driver], "None")
VAR LowValue = SELECTEDVALUE(SensitivityDrivers[LowValue], 0)

-- Apply only the selected driver at its low value; others at base case
VAR BaseVolumeGrowth = 0.03
VAR BasePriceAdj = 0.00
VAR BaseCostInflation = 0.05

VAR PriceAdj = IF(CurrentDriver = "Price Adjustment", LowValue, BasePriceAdj)
VAR VolumeGrowth = IF(CurrentDriver = "Volume Growth", LowValue, BaseVolumeGrowth)
VAR CostInflation = IF(CurrentDriver = "Cost Inflation", LowValue, BaseCostInflation)

RETURN
SUMX(
    Sales,
    (Sales[Units Sold] * (1 + VolumeGrowth) * Sales[Unit Price] * (1 + PriceAdj))
    - (Sales[Units Sold] * (1 + VolumeGrowth) * Sales[Unit Cost] * (1 + CostInflation))
)

Sensitivity High Margin = 
VAR CurrentDriver = SELECTEDVALUE(SensitivityDrivers[Driver], "None")
VAR HighValue = SELECTEDVALUE(SensitivityDrivers[HighValue], 0)

VAR BaseVolumeGrowth = 0.03
VAR BasePriceAdj = 0.00
VAR BaseCostInflation = 0.05

VAR PriceAdj = IF(CurrentDriver = "Price Adjustment", HighValue, BasePriceAdj)
VAR VolumeGrowth = IF(CurrentDriver = "Volume Growth", HighValue, BaseVolumeGrowth)
VAR CostInflation = IF(CurrentDriver = "Cost Inflation", HighValue, BaseCostInflation)

RETURN
SUMX(
    Sales,
    (Sales[Units Sold] * (1 + VolumeGrowth) * Sales[Unit Price] * (1 + PriceAdj))
    - (Sales[Units Sold] * (1 + VolumeGrowth) * Sales[Unit Cost] * (1 + CostInflation))
)

Sensitivity Range = 
[Sensitivity High Margin] - [Sensitivity Low Margin]

Put SensitivityDrivers[Driver] on a table visual's rows, then add [Sensitivity Low Margin], [Sensitivity High Margin], and [Sensitivity Range] as columns. Sort by [Sensitivity Range] descending — the driver with the widest range is your biggest risk/opportunity lever.

Tip: Hardcoding base case values directly in DAX (as shown above with 0.03, 0.00, 0.05) creates a maintenance problem. A better pattern is to create a separate BaseCase table using DATATABLE with the same structure as your Scenarios table, and read from it using LOOKUPVALUE(). That way, if your base case assumptions change, you update one table — not ten measures.

---

Handling Compound Effects and Non-Linear Logic

Real business models have non-linear behaviors that simple parameter multiplication misses. Price elasticity is a classic example: if you raise prices, volume typically drops. Modeling them as independent levers overstates the optimistic scenario.

You can encode this relationship directly into your measures:

-- Assume price elasticity of -1.5 (a 10% price increase leads to 15% volume decline)
Price Elasticity Constant = -1.5

Elasticity-Adjusted Volume Growth = 
VAR RawVolumeGrowth = [Active Volume Growth]
VAR PriceAdj = [Active Price Adj]
VAR ElasticityEffect = [Price Elasticity Constant] * PriceAdj
RETURN
RawVolumeGrowth + ElasticityEffect

Elasticity-Adjusted Revenue = 
VAR PriceAdj = [Active Price Adj]
VAR AdjVolumeGrowth = [Elasticity-Adjusted Volume Growth]
RETURN
SUMX(
    Sales,
    Sales[Units Sold] * (1 + AdjVolumeGrowth) * Sales[Unit Price] * (1 + PriceAdj)
)

Now when a user increases the price slider, the model automatically reduces the implied volume — because that's how markets actually work. You can make the elasticity constant itself a What-If Parameter, which lets stakeholders explore "what if our customers are less price-sensitive than we assumed?"

Warning: Be explicit with stakeholders when your model encodes assumptions like elasticity. If they don't know the model is adjusting volume based on price, they'll be confused when the two sliders don't behave independently. Add a card visual that shows the effective volume growth rate after elasticity adjustment, clearly labeled.

---

Real-World Project: Building a SaaS Revenue Forecast Model

Let's pull everything together with a complete scenario that a SaaS company's finance team would actually use. The business needs to project Annual Recurring Revenue (ARR) based on:

• Starting ARR (from actuals)
• New logo growth rate
• Expansion revenue rate (existing customer upsell)
• Churn rate

Assume the SaaS_Metrics table has: [Month], [Starting ARR], [New Logo ARR], [Expansion ARR], [Churned ARR], [Ending ARR].

Create four What-If Parameters:

• New Logo Growth %: -20% to 50%, increment 1%, default 0%
• Expansion Rate %: -10% to 30%, increment 1%, default 0%
• Churn Rate Change %: -30% to 30%, increment 1%, default 0% (positive = more churn)
• Forecast Periods: 1 to 24 (months), increment 1, default 12

Build the projection engine:

-- Cache the active period selection
Active Forecast Periods = [Forecast Periods Value]

-- Base period averages (trailing 3 months for stability)
Avg Monthly New Logo ARR = 
AVERAGEX(
    TOPN(3, ALL(SaaS_Metrics[Month]), SaaS_Metrics[Month], DESC),
    SaaS_Metrics[New Logo ARR]
)

Avg Monthly Expansion ARR = 
AVERAGEX(
    TOPN(3, ALL(SaaS_Metrics[Month]), SaaS_Metrics[Month], DESC),
    SaaS_Metrics[Expansion ARR]
)

Avg Monthly Churn ARR = 
AVERAGEX(
    TOPN(3, ALL(SaaS_Metrics[Month]), SaaS_Metrics[Month], DESC),
    SaaS_Metrics[Churned ARR]
)

-- Starting point for the forecast is the most recent ending ARR
Latest Ending ARR = 
CALCULATE(
    LASTNONBLANK(SaaS_Metrics[Ending ARR], 1),
    ALL(SaaS_Metrics[Month])
)

-- Projected monthly components with parameter adjustments
Projected Monthly New Logo = 
[Avg Monthly New Logo ARR] * (1 + [Param_NewLogoGrowth])

Projected Monthly Expansion = 
[Avg Monthly Expansion ARR] * (1 + [Param_ExpansionRate])

Projected Monthly Churn = 
[Avg Monthly Churn ARR] * (1 + [Param_ChurnRateChange])

-- Net new ARR per month
Projected Net New Monthly ARR = 
[Projected Monthly New Logo] + [Projected Monthly Expansion] - [Projected Monthly Churn]

-- Forward ARR at end of forecast horizon
Projected Ending ARR = 
[Latest Ending ARR] + 
([Projected Net New Monthly ARR] * [Active Forecast Periods])

-- Implied ARR growth over the horizon
Projected ARR Growth % = 
DIVIDE(
    [Projected Ending ARR] - [Latest Ending ARR],
    [Latest Ending ARR],
    0
)

-- Net Revenue Retention (a key SaaS health metric)
Projected NRR = 
DIVIDE(
    [Projected Monthly Expansion] - [Projected Monthly Churn] + [Avg Monthly New Logo ARR],
    [Avg Monthly New Logo ARR] + [Avg Monthly Expansion ARR],
    0
)

Dashboard layout for this model:

Place four sliders at the top of the page. Below them, show:

• A KPI card for [Projected Ending ARR] with [Latest Ending ARR] as the comparison value
• A KPI card for [Projected ARR Growth %]
• A KPI card for [Projected NRR]
• A table showing the three scenarios (using your Scenarios table adapted for SaaS metrics) side by side
• A bar chart using SensitivityDrivers to show which lever has the most impact on ARR

Tip: Lock the sliders to "single select" in the Format pane and set a default value that matches your Base Case scenario. That way, when the report first opens, stakeholders see the expected outcome — not a zeroed-out model.

---

Performance Considerations

What-If Parameter models can get slow. Here's why, and what to do about it.

The SUMX problem: Every time a slider changes, every SUMX measure recalculates across your entire dataset. If your Sales table has 5 million rows and you have 8 projected measures, that's 40 million row-level calculations on every slider move. This becomes painful quickly.

Solution — pre-aggregate in your measures: Instead of SUMX over the raw transaction table, SUMX over a pre-aggregated structure. Create a calculated table that summarizes your raw data:

Sales_Aggregated = 
SUMMARIZE(
    Sales,
    Sales[Date],
    Sales[Product],
    "TotalUnits", SUM(Sales[Units Sold]),
    "TotalRevenue", SUMX(Sales, Sales[Units Sold] * Sales[Unit Price]),
    "TotalCost", SUMX(Sales, Sales[Units Sold] * Sales[Unit Cost])
)

Then write your projected measures against Sales_Aggregated instead of Sales. If your raw table has 5 million rows but only 3,000 unique date-product combinations, your SUMX now iterates 3,000 rows instead of 5 million.

Use Import mode: What-If models should never be built on DirectQuery. DirectQuery generates SQL queries for every calculation; parameter changes trigger cascading query re-execution against your database. Import mode keeps everything in the VertiPaq engine in memory, where SUMX operations are dramatically faster.

Reduce parameter precision: Do you really need a slider that goes from 0% to 20% in 0.1% increments? That's 201 rows in your parameter table. Changing to 0.5% increments cuts it to 41 rows. This doesn't matter much for a single parameter, but when you have five parameters all interacting, keeping tables small reduces model complexity.

---

Hands-On Exercise

Build the following in a new Power BI Desktop file:

1. Create a simple Sales table using Enter Data with at least 20 rows of product sales data (Product, Date, Units, UnitPrice, UnitCost). Make it feel realistic — three products, six months of data.

2. Create two What-If Parameters: Discount Rate % (0% to 30%, 1% increments, default 5%) and Volume Lift % (-10% to 20%, 1% increments, default 0%).

3. Build these measures:

   • Base Revenue
   • Discounted Revenue (applies the discount parameter to unit price before summing)
   • Lift-Adjusted Revenue (applies volume lift on top of discount)
   • Revenue Impact (the delta between base and lift-adjusted)
   • Revenue Impact %

4. Create a Scenarios disconnected table with three rows: Conservative (10% discount, -5% volume), Realistic (5% discount, 5% volume), Aggressive (2% discount, 15% volume).

5. Build Active Discount Rate and Active Volume Lift measures that read from the scenario table when a scenario is selected, and from the parameter sliders otherwise.

6. Build a report page with: both sliders, a scenario slicer, three KPI cards (Base Revenue, Adjusted Revenue, Revenue Impact), and a bar chart showing Revenue by Product using Lift-Adjusted Revenue.

7. Verify that selecting "Aggressive" in the scenario slicer updates all cards correctly, and that manually moving the sliders works when no scenario is selected.

---

Common Mistakes & Troubleshooting

Problem: The parameter slicer shows the correct value, but measures don't change.

This almost always means your projected measure is referencing the wrong measure name. Double-check that your measure uses [Param_PriceAdj] (or whatever you named your parameter measure), not the table column name 'Price Adjustment %'[Price Increase %]. Using the column directly inside SELECTEDVALUE without the table context will return blank or the full list of values.

Problem: When no scenario is selected, the model shows 0 for everything.

Your Active parameter measures are returning 0 because the SELECTEDVALUE on the Scenarios table is returning blank (nothing selected), and your IF logic treats blank as "Custom" — then reads the parameter slider, which is also at 0. Fix this by setting sensible defaults on all your Param_ measures, or by pre-selecting your Base Case scenario in the slicer using "Set as default" in the slicer's format options.

Problem: The sensitivity table shows the same value for every driver.

The SELECTEDVALUE on SensitivityDrivers[Driver] is returning blank when the table visual has multiple rows — because you have all rows displayed, each row is filtered to its own driver in the visual's row context, but SELECTEDVALUE needs a single slicer-style selection. For table visuals, use MAX(SensitivityDrivers[Driver]) instead of SELECTEDVALUE() to read the row context value correctly.

Problem: Sliders are extremely slow to respond.

First, check if you're on DirectQuery — switch to Import mode. Second, look for SUMX measures iterating over large raw tables; pre-aggregate as described above. Third, check if you have cross-filtering enabled between your parameter tables and other tables — parameter tables should have no relationships, and their slicers should not cross-filter other visuals.

Problem: Scenario assumptions show up in wrong measures.

If "Best Case" assumptions are bleeding into historical actuals, you have a filter context problem. Your Scenarios table is likely cross-filtering something it shouldn't. Check the Edit Interactions settings and disable cross-filtering from your scenario slicer to any visuals displaying historical data.

---

Summary & Next Steps

You've now built something genuinely useful — a forward-looking analytical model that lives inside Power BI rather than a separate Excel file. Here's what you've covered:

• The mechanics of What-If Parameters (GENERATESERIES + SELECTEDVALUE) and why disconnected tables are the right pattern for this work
• How to build multi-variable projected measures that stay clean and performant with VAR caching
• How to combine named scenario switching with continuous sliders, so stakeholders have both precision and simplicity
• How to build a tornado/sensitivity analysis that shows which assumptions matter most
• Performance patterns that keep large models responsive

Where to go from here:

If you want to deepen this work, explore Calculation Groups — they're a more advanced technique that lets you switch between measure variants (Actual vs. Projected vs. Budget) without duplicating every measure in your model. Tabular Editor 3 makes this approachable.

For the forecasting direction, look into integrating Python or R visuals to apply statistical forecasting (ARIMA, exponential smoothing) and feeding those outputs back into Power BI as a reference dataset alongside your parameter-driven manual projections. This gives you both algorithmic baselines and business-assumption overlays in the same report.

Finally, once stakeholders start loving these models, you'll face a new problem: they'll want to save scenarios. Explore Power BI's integration with Dataflows and writeback capabilities (via third-party tools like ACTERYS or native Premium features) to let users persist their scenario assumptions across sessions.