Graphed LogoGraphed
Login

How to Do Sensitivity Analysis in Power BI

Cody Schneider•

Thinking about the future of your business often leads to a lot of "what-if" questions. What if our costs go up by 10%? What if our new marketing campaign drives 20% more sales? Answering these questions is the core of sensitivity analysis, a powerful technique for understanding how changes in key variables impact your bottom line. This guide will show you exactly how to perform sensitivity analysis in Power BI, turning your static reports into interactive tools for planning and decision-making.

What Exactly is Sensitivity Analysis?

Sensitivity analysis, often called "what-if" analysis or scenario planning, is a method used to determine how different values of an independent variable will impact a particular dependent variable under a given set of assumptions. In simpler terms, it's a way to see how sensitive your results are to changes in your inputs.

Here’s a practical example: Imagine you run an e-commerce store. You want to forecast your profit for the next quarter. Your profit depends on several factors, including your sales volume, the cost of goods sold, and your marketing spend. Sensitivity analysis allows you to model questions like:

  • How would a 5% increase in traffic from our ads affect our total profit?

  • What is our break-even point if our shipping costs rise by 15%?

  • If we offer a 10% discount across the store, how much additional sales volume would we need to generate the same amount of profit?

Running this analysis helps you prepare for uncertainty, identify risks, and spot opportunities. It transforms your dashboard from a simple historical record into a forward-looking decision-making cockpit, highlighting which levers you can pull that will have the biggest impact on your success.

Getting Started: Your Data Model and Base Measures

Before you can start modeling scenarios, you need a solid foundation. For this tutorial, we'll use a simple sales data model. Let’s assume you have a table named Sales that contains columns like OrderDate, ProductCategory, UnitsSold, SalesAmount, and CostAmount.

The first step is always to create explicit measures for your key metrics. Using measures instead of dragging raw columns into your visuals is a fundamental Power BI best practice that makes your models more scalable and powerful.

Let's create two foundational measures:

1. Total Sales:

2. Total Profit:

Once you have these base measures, you have a reliable starting point. All our "what-if" calculations will build upon these core numbers.

Creating an Interactive Slicer with "What If" Parameters

The magic behind interactive sensitivity analysis in Power BI is a feature called "What If" Parameters. This feature allows you to create a dynamic slicer that users can interact with, and the selected value can be fed directly into your DAX measures.

Here's how to set one up step-by-step:

Step 1: Go to the Modeling Tab

In the Power BI Desktop ribbon, navigate to the Modeling tab.

Step 2: Create a New Parameter

Click on New Parameter and select Numeric range. This will open a dialog box where you can configure your scenario driver.

Step 3: Configure Your Parameter

Let's configure a parameter to simulate a percentage change in sales. We'll fill in the fields as follows:

  • Name: Sales Growth Assumption

  • Data type: Decimal number (This is best for percentages).

  • Minimum: -1 (This represents a -100% change).

  • Maximum: 1 (This represents a +100% change).

  • Increment: 0.05 (This will create steps of 5%).

  • Default: 0 (So no change is applied by default).

Crucially, make sure the box for Add slicer to this page is checked. When you click OK, Power BI automatically does three things:

  1. It creates a new slicer on your report page.

  2. It generates a new calculated table named Sales Growth Assumption with a single column of values from your minimum (-1) to your maximum (1) at the specified increment (0.05).

  3. It creates a DAX measure called Sales Growth Assumption Value, which captures the current value selected on the slicer.

The DAX for this measure looks like this:

This measure is the bridge between your user interface (the slicer) and your data model. We recommend formatting the slicer to show the numbers as a percentage for a better user experience. Simply select the slicer, go to the Format pane, and under "Slicer settings > Numeric inputs", or select the column in the data pane, go to the Column tools ribbon, and change the format to "Percentage."

Connecting Your Parameter to DAX Measures

Now that you have your interactive slicer and a measure that holds its value, you need to infuse this logic into your business calculations. This is done by creating new "what-if" measures.

Let's create a Forecasted Sales measure that reacts to the slicer. We want it to take our base Total Sales and adjust it by the percentage selected in the Sales Growth Assumption slicer.

Create a new measure with the following DAX formula:

Let's break that down:

  • [Total Sales] is our baseline number.

  • (1 + [Sales Growth Assumption Value]) takes the slicer value (e.g., 0.10 for 10%) and adds it to 1, creating a multiplier (1.10). If the slicer is set to -15% (-0.15), the multiplier becomes 0.85.

That’s it! Now, the Forecasted Sales measure is dynamically linked to the slicer. As a user adjusts the slider, the value of this measure recalculates instantly.

You can create more complex scenarios as well. For example, let's create a Forecasted Profit. Let's assume that when sales increase, your costs also increase, but perhaps at half the rate of your sales growth.

First, forecast your costs:

Then, calculate the profit based on your forecasted numbers:

This multivariable approach lets you build much richer and more realistic models.

Visualizing Your What-If Analysis

With your measures in place, it's time to bring your sensitivity analysis to life. Here are a few effective ways to visualize the results.

1. KPI Cards

The simplest way to show the effect is with KPI cards. Place a card showing your original Total Sales next to a card showing your dynamic Forecasted Sales. As you move the slicer, you'll see the Forecasted card update, giving a clear, immediate comparison.

2. Tables and Matrices

Tables are great for seeing the impact across different categories. Create a table with ProductCategory and then add columns for Total Sales, Forecasted Sales, and even a new measure for the difference or percentage variance.

Your table will instantly update as the slicer moves, showing which categories are most impacted by a potential rise or fall in sales.

3. Line Charts for a Sensitivity Curve

For a fantastic visual representation of sensitivity, you can create a line chart that plots the full range of possible outcomes.

  • Add a Line chart to your canvas.

  • Drag the Sales Growth Assumption column (from the parameter table) to the X-axis.

  • Drag your Forecasted Sales measure to the Y-axis.

This generates a line that shows how forecasted sales will change across the entire spectrum of your slicer's range (-100% to +100%). This "sensitivity curve" instantly tells you whether the relationship is linear and shows the magnitude of change at a glance. It's an exceptionally clear way to present the range of possible outcomes to stakeholders.

Practical Tips for Better Scenario Modeling

Once you are comfortable with the basics, here are a few things you can do to level up your analysis.

Tip 1: Use Multiple "What-If" Slicers

Don’t limit yourself to one variable. You can create multiple parameters. For instance, add another parameter for 'Cost Increase %'. Then, blend them into your measures. This allows you to model how a simultaneous change in sales drivers and cost drivers would affect profit.

Tip 2: Combine with Bookmarks

Set your slicers to specific states — for example, a "Worst Case" (e.g., -20% growth), an "Expected Case" (e.g., +5% growth), and a "Best Case" (e.g., +20% growth). Create a Power BI bookmark for each state. You can then add buttons to your report that allow users to instantly jump between these predefined scenarios without having to manually adjust the slicers, which is excellent for presentations.

Tip 3: Give Your Reports Clear Instructions

Add a text box to your report page explaining what the slicer does. Something simple like, "Use the slider below to simulate a potential change in sales volume and see its effect on forecasted profit." This ensures anyone viewing the report understands how to interact with it.

Final Thoughts

By using Power BI's "What If" parameters and a few lines of DAX, you can transform your reporting from a historical overview into a powerful, interactive forecasting tool. This technique empowers you and your team to explore possibilities, quantify risks, and make business decisions based on a deeper understanding of the numbers that drive your performance.

All we want to do is make data easier to work with. That’s why we created Graphed, because getting answers from your data shouldn't require you to become a DAX expert or learn a complex new program. We connect your data platforms and let you ask questions in plain English—like "forecast our profit if sales jump by 15% but our ad costs increase by 10% next quarter"—and Graphed instantly builds the scenario analysis for you, letting you test ideas in seconds instead of hours of setup.