What If Scenarios in Power BI

Building a powerful dashboard is one thing, but turning it into a dynamic forecasting tool is another. If you've ever found yourself asking "what would happen if...?" while looking at your data, you're ready for What-If scenarios in Power BI. This feature lets you, your stakeholders, and your team interact with reports to see how changing key variables - like pricing, ad spend, or costs - could impact your bottom line. We will walk through exactly how to create and use these interactive parameters to bring your data to life.

What is 'What-If' Analysis?

At its core, what-if analysis (or scenario analysis) is a way to test different outcomes by changing certain variables in your data model. Instead of looking at past performance in a static report, you can actively explore future possibilities. It transforms a report from a historical record into a forward-looking decision-making tool.

Think about these common business questions:

• What would a 5% price increase do to our overall revenue?
• How will our profit margin change if supply costs go up by 10%?
• If we increase our marketing budget by $20,000, how many new leads can we project?

Answering these questions typically involves exporting data, building complex spreadsheet models, and hoping your formulas are correct. Power BI's What-If parameter feature simplifies this entire process, allowing you to build these scenarios directly into your dashboards with interactive sliders that anyone can use.

Creating a What-If Parameter in Power BI: A Step-by-Step Guide

Let’s build a common what-if scenario from scratch: modeling the impact of a price increase on total sales. For this, we'll assume you have a Power BI report with a simple sales dataset that includes a measure called [Total Sales].

Step 1: Locate the "New Parameter" Option

First, open your report in Power BI Desktop. In the top ribbon, navigate to the Modeling tab. Here, you'll find the New parameter button. Click on it, and from the dropdown, select Numeric range.

Step 2: Configure Your Parameter

A dialog box will pop up where you define the behavior of your new parameter. This is where you set the name, data type, and range for your interactive slicer.

Let's configure it for our price increase scenario:

• Name: Let's call it Price Increase %. This name will be used for the table and measure Power BI creates.
• Data type: Choose Decimal number since we want to work with percentages.
• Minimum: Set this to -0.10 to allow for a potential price decrease of 10%.
• Maximum: Let's set it to 0.25, representing up to a 25% price increase.
• Increment: Set this to 0.01. This means each step of the slider will adjust the value by 1%.
• Default: We can set this to 0 or leave it blank. This is the value the slider will be set to initially.

Make sure the Add slicer to this page box is checked. This will automatically add a handy slider to your report canvas, saving you a step.

Once you click "OK," see what happens. Power BI automatically does two things for you in the background.

Step 3: Understand What Power BI Just Created

When you created the parameter, Power BI added two new components to your data model, which you can see in the Data pane on the right:

1. A New Calculated Table

Power BI generated a new table named Price Increase %. This table isn't connected to any of your other data sources. It's a standalone table that contains a single column of calculated values based on the range you defined (-0.10 to 0.25, incrementing by 0.01). The DAX formula used to create this table is:

Price Increase % = GENERATESERIES(-0.1, 0.25, 0.01)

This table is what populates the interactive slicer that was added to your report canvas.

2. A New Measure

Power BI also created a measure within this new table. This measure is designed to capture the single value currently selected by the slider. The DAX formula for it looks like this:

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

The SELECTEDVALUE() function does exactly what it sounds like: it checks the 'Price Increase %' column and returns the value you've selected with the slicer. If no value is selected or multiple values are somehow selected, it returns the optional second argument, which is 0 in this case. This measure is the bridge between the interactive slider and your other report calculations.

Applying the What-If Parameter in Your Calculations

Our slicer exists, but moving it doesn't affect anything... yet. To bring the what-if scenario to life, you need to create a new DAX measure that uses the parameter's SELECTEDVALUE measure.

Let’s create a Projected Sales measure that calculates the potential sales with the price adjustment from our slider.

1. Create a New Measure:

Right-click on your primary sales table (or any table where you keep your measures) and select New measure.

2. Write the DAX Formula:

In the formula bar, type the following expression:

Projected Sales = [Total Sales] * (1 + 'Price Increase %'[Price Increase % Value])

Let's break this down:

• [Total Sales] is your existing measure for your current total sales.
• 'Price Increase %'[Price Increase % Value] is the measure that returns the current value from our slider (e.g., 0.05 for a 5% increase).
• (1 + 'Price Increase %'[Price Increase % Value]) effectively creates our multiplier. If you select 5% on the slider, this part of the formula becomes (1 + 0.05), or 1.05.

Essentially, you are telling Power BI to take the current sales and multiply them by the factor determined by your slider.

3. Add the New Measure to Your Visuals:

Now, you can visualize the impact. A good way to do this is with a Card visual. Drag your new Projected Sales measure onto a card. Place it next to a card showing your original [Total Sales]. Now, interact with the Price Increase % slicer. As you slide it back and forth, you'll see the Projected Sales value update in real-time, while your Total Sales card remains unchanged. You've successfully built a dynamic scenario planning tool!

Practical Use Cases & Advanced Tips

Once you've mastered a single parameter, you can start combining them to model more complex business scenarios.

Combining Multiple What-If Parameters

Imagine your business wants to know how a combination of a price discount and an increase in ad spend would affect overall profit. You can model this by creating two separate what-if parameters:

• A Discount % parameter (0% to 30%, decimal).
• An Extra Ad Spend parameter ($0 to $100,000, whole number).

You can then create a new Projected Profit measure that incorporates both slider values:

Projected Profit = ( ( [Total Sales] * (1 - 'Discount %'[Discount % Value]) ) - [Total Costs] ) - 'Extra Ad Spend'[Extra Ad Spend Value]

With this, you can now adjust both the discount level and the marketing spend independently to find the sweet spot that maximizes your projected profit.

Using Buttons and Bookmarks for Pre-Set Scenarios

Sliders are great for exploration, but sometimes you want to present clearly defined scenarios, such as "Best Case," "Worst Case," and "Most Likely Case." Power BI's Bookmarks feature is perfect for this.

1. Set Your Worst Case Scenario: Adjust your what-if slicers to the values that represent your worst case (e.g., maximum costs, lowest sales price increase).
2. Create a Bookmark: Go to the View tab, open the Bookmarks pane, and click Add. Rename your new bookmark to "Worst Case Scenario." Importantly, in the bookmark's ellipses (...) options, make sure to set it so it only applies to 'Data' - this ensures it only saves the slicer values, not other report filters.
3. Repeat for Other Scenarios: Do the same for your "Most Likely" and "Best Case" scenarios.
4. Add Buttons: Go to the Insert tab and add three buttons to your report. Assign each button an action to trigger one of your bookmarks. Now, users can simply click a button to instantly see a pre-configured outlook without having to adjust the sliders manually.

Thinking Beyond Financials

What-if parameters aren't just for money. You can use them to model almost any variable:

• Operations: What if we hire 5 new support agents? How would that affect average ticket resolution times?
• Marketing: What if our website conversion rate improves by 0.5%? How many additional leads would that generate?
• Sales: If we increase sales outreach by 15%, what's the projected quarterly pipeline?

Final Thoughts

Power BI's What-If feature empowers you to turn standard reports into interactive, strategic tools. By introducing parameters, you give your team the ability to model scenarios, test assumptions, and make data-driven decisions about the future, all without ever leaving the dashboard.

The time-consuming part of a project like this is often just getting all your data sources connected and building the foundational reports. At Graphed, we help you overcome that first big hurdle. By connecting your tools and using simple, natural language to ask questions, you can generate real-time dashboards in seconds, not hours. This gets the heavy lifting out of the way, freeing you up to focus on more advanced analysis like the what-if scenarios we just built.