How to Use What-If Analysis in Power BI

Wishing you could peek into the future to see how a price change might affect your revenue or how adjusting your ad budget could impact sales? With Power BI's What-If analysis, you can stop guessing and start modeling different scenarios directly in your reports. This article will guide you step-by-step through setting up and using what-if parameters to bring powerful forecasting capabilities to your dashboards.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is What-If Analysis?

What-if analysis, sometimes called sensitivity or scenario analysis, is a powerful technique for seeing how changing a variable affects your overall data. Instead of being stuck with static reports showing what has happened, what-if analysis allows you to create interactive controls - like sliders - to see what could happen under different conditions.

Imagine you're a marketing manager. You could ask questions like:

• "What if we increase our ad spend by 10%? What is our projected return on investment?"
• "What if our website conversion rate improves by 0.5%? How many more leads would we get?"
• "What if we offer a 15% discount for a weekend sale? How will that impact our total revenue?"

Traditionally, answering these questions meant tedious work in Excel, creating multiple copies of a spreadsheet just to tweak one number. In Power BI, you can build this interactivity directly into your dashboard, allowing anyone to explore potential outcomes in real-time.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Set Up Your First What-If Parameter in Power BI

Let’s build a what-if analysis from scratch. For this example, we’ll analyze how a potential price increase could affect total revenue. We'll create a slicer that lets us adjust a future price increase from 0% to 25%.

Step 1: Navigate to the Modeling Tab

Open your Power BI report. At the top of the ribbon, click on the Modeling tab. This is where you’ll find the tools for creating data models, measures, and, of course, what-if parameters.

Step 2: Create a New Parameter

In the Modeling ribbon, find the section labeled "Parameters" and click on New parameter. Since we are working with a range of numbers (from a 0% to 25% price increase), select Numeric range from the dropdown menu.

You’ll see a "What-if parameter" window pop up with several fields to configure. Let's fill them out for our pricing scenario:

1. Name: This is the name of both the table and the slicer that Power BI will create. Let’s call it 'Price Increase %'.
2. Data type: You can choose Whole number, Decimal number, or Fixed decimal number. Since percentages often involve decimals, let's select Decimal number.
3. Minimum: The lowest value for our slider. For no price increase, this will be 0.
4. Maximum: The highest value we want to simulate. Let's set this to 0.25 to represent a 25% increase.
5. Increment: This determines how much the value changes each time you move the slider. A smaller number gives you more precision. Let's set it to 0.01 for 1% increments.
6. Default: The starting value when you first open the report. Let's start with 0 (no increase).

Finally, make sure the box for "Add slicer to this page" is checked. This will automatically add the interactive slider to your report canvas.

Your configuration should look something like this. Once you're done, click Create.

What Power BI Creates for You

When you click "Create," Power BI does two things in the background for you:

1. It creates a new calculated table using a DAX formula called 'GENERATESERIES'. This table contains a single column with all the possible values you defined (from 0 to 0.25, in increments of 0.01).
2. It creates a DAX measure within that same table to capture the value currently selected on the slider. The DAX code for this measure is:

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

This simple formula checks which value is currently selected in the 'Price Increase %' slicer. If only one value is selected, it returns that value, otherwise, it returns the default value we set, which was 0.

You can see the new table and measure in your Data pane on the right side of the screen.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Putting Your What-If Parameter to Work with DAX

Now that we have our slider, we need to connect it to our existing data. To do this, we'll write a new DAX measure that uses the 'Price Increase % Value' we just created.

Let's assume you already have a basic measure for total revenue:

Total Revenue = SUM(Sales[Revenue])

Now, let's create a new measure to calculate the "what-if" or forecasted revenue. You can do this by right-clicking your sales table and selecting New measure.

In the formula bar, enter this DAX formula:

Forecasted Revenue = [Total Revenue] * (1 + 'Price Increase %'[Price Increase % Value])

Let's break down that formula:

• [Total Revenue] is our existing, baseline revenue calculation.
• 'Price Increase %'[Price Increase % Value] is the measure that grabs the current value from our slider. If a 10% increase is selected on the slider, this measure will return 0.10.
• (1 + 'Price Increase %'[Price Increase % Value]) handles the percentage calculation. Adding 1 to the decimal (e.g., 1 + 0.10) gives us a multiplier of 1.10, which correctly calculates a 10% increase.

Press Enter to save your new measure. You now have everything you need to visualize your what-if analysis.

Visualize the Impact of Your Scenarios

The final step is to bring it all together on your report canvas. Since you checked "Add slicer to this page," you should already have the slider for 'Price Increase %' on your report.

1. Add Cards to Show Key Numbers: Drag two Card visualizations onto your report. In the first card, add the original 'Total Revenue' measure. In the second card, add the new 'Forecasted Revenue' measure.
2. Create a Chart to Compare: Add a bar chart or line chart to the canvas.

Now, interact with the slider! As you drag the handle for 'Price Increase %', you’ll see the 'Forecasted Revenue' card and chart update instantly. This gives you and your stakeholders a clear, dynamic view of how different pricing strategies could play out.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

More Examples of What-If Analysis in Action

The concept is flexible and can be applied to almost any business area. Here are a few more ideas to get you started:

• Marketing Budget Planning: Create a "marketing budget" what-if parameter. Use it to model how changes in ad spend might affect lead generation, assuming a certain cost per lead. Example Formula: 'Projected Leads = ('Marketing Budget'[Budget Value]) / [Average Cost Per Lead]'
• E-commerce Conversion Rate Modeling: Model how a potential improvement in your website's conversion rate would impact online sales. Example Formula: 'Projected Sales = [Total Website Sessions] * ('Conversion Rate %'[CR % Value])'
• Sales Commissions Forecasting: Create a slicer for "Commission Percentage" to calculate the total commissions payable to your team under different compensation plans. Example Formula: 'Total Commissions = [Total Revenue] * ('Commission %'[Commission % Value])'

A Few Final Tips for Success

• Start Simple: Your first what-if analysis doesn't need to be overly complex. Answering one simple question with an interactive slicer is more powerful than building a convoluted model that no one understands.
• Clearly Label Everything: Make sure the title of your slicer (e.g., 'Price Increase Forecast') and the titles of your charts are clear. Your audience should know exactly what they are manipulating.
• Combine Multiple Parameters: For advanced analysis, you can add more than one what-if parameter to a page. For example, you can have one slicer for ad spending increase and another for expected conversion rate to see how they impact each other.

Final Thoughts

Creating what-if scenarios in Power BI is a powerful way to transform your reports from static historical documents into dynamic, forward-looking decision-making tools. By incorporating interactive sliders and a couple of simple DAX measures, you can allow your team members to model different outcomes, ask meaningful strategic questions, and make more informed decisions.

While Power BI offers incredible depth for custom scenario modeling, there is a steep learning curve to writing DAX code and structuring the data correctly. Sometimes, this process of building out manual calculations can feel like report-building drudgery. At Graphed, we help you skip the technical hurdles entirely. By connecting our marketing and sales data sources, you can simply talk to your data in natural language - instead of spending hours writing DAX, you could ask a question like, "What would my revenue look like if we increased prices by 5%?" and get an instant dashboard built for you.