How To Use ‘What-If’ Parameters In Power BI

When it comes to data-driven decision making, the ability to interactively tweak parameters and immediately see the potential outcomes is a game-changer for modern businesses.

For example

Imagine being able to look at a dashboard that not only showcases your company's revenue, but being able to ask - and answer - the question:

"What would our revenue look like if we changed how much we discount our products?"

• Would we make more sales and more profit?
• Or would we just see a net loss?
• What about if we increased the price instead?...

Well, with Power BI, you can know all these answers and more!

Suddenly you no longer have to just guess or rely on the HiPPO (Highest Paid Person's Opinion) when it comes to business decisions. Instead, you can get actual predicted results that you can use to work from and take action.

Now obviously, the value of being able to do this is huge, and with some tools - it can be quite complicated to set up. However, with Power BI, implementing this functionality is a lot easier than you might think.

In this guide, I’m going to walk you through exactly how to set all this up from start to finish, using ‘what-if’ parameters in Power BI. This way, you’ll be able to get more insights than ever before from your data.

Grab a coffee and let’s dive in…

What is a ‘what if’ parameter?

A what-if parameter is essentially a user-controlled value that can be plugged into a formula or measure. These parameters then allow users to play with specific variables and conditions in a Power BI report, and then predict outcomes based on those variables.

This means that instead of merely providing a static snapshot of your data, you can present dynamic capabilities, hypothesize various scenarios, and plan business decisions.

It may not seem like much of a change, but the ability to go from a passive recipient of information, to then become active participants and tease out insights is game changing.

Sidenote: By the end of this guide, if you’re still struggling to use these features, or simply want to learn more about Power BI and how it can help your business, (or you want to learn it and become a Business Intelligence Analyst), then check out my Power BI course.

No prior knowledge required. This course will take you from absolute beginner in Power BI to being able to get hired in 2024 as a confident and effective Business Intelligence Analyst.

You'll learn the full Power BI suite of tools, the M programming language, and the DAX formula language, all while analyzing real-world datasets and building enterprise-level projects.

With that out of the way, let’s walk through how to use this feature.

Getting started with what-if parameters

For the purposes of this tutorial, I’m going to show you how to transform a plain-old vanilla Power BI report into a scenario-testing machine.

Don’t worry though. Although the core concept might seem daunting, Power BI is incredibly user-friendly, and offers built-in support for creating what-if parameters.

This means you won't be starting from scratch, but instead leveraging predefined widgets tailored for this exact purpose, so let’s walk through an example.

Creating a new "Loyalty Member" what-if parameter

Start off by launching Power BI and opening a report.

Access the modeling tab

In the top header menu you'll find a tab labeled "Modeling”. Go ahead and click on it, and a new menu will appear below.

Initiate the new parameter

Inside this Modeling tab menu, there should be an option called "New Parameter."

Click on this and then select “Numeric range” from the drop-down list. This will open up a dialog box designed specifically for setting up what-if scenarios, like so:

Again, no DAX formulas are needed at this stage, making setup surprisingly straightforward.

OK, let's fill this out real quick:

Naming the parameter

Go ahead and give your parameter a name.

Don’t skip this. This name will not help you quickly identify the parameter in the future, which is especially helpful if your report evolves to contain multiple complex parameters, so be sure to give it something memorable.

If you want to follow this exactly, then I recommend something like “Loyalty Member Discount”, as that’s the first what-if parameter that we’ll set up.

OK, so let’s walk through the rest of these boxes, so we can configure the parameter's values and define its boundaries.

Why care about this?

Well, by choosing this range strategically, it will ensure that the user has a meaningful and realistic array of scenarios to explore with the live data.

Set the data type

A common oversight when configuring parameters is forgetting to adjust their data type.

If you're using decimal numbers for the parameter values (like 0.2 for 20%), make sure that you select “decimal number” as the parameter’s data type, rather than the default of “whole number”, or your parameter won’t work as expected!

Set the minimum value

This box represents the lowest possible value your parameter can take.

For example

In our "Loyalty Member Discount" scenario, the most conservative option might be to offer no discount, meaning our minimum value would be zero - as that’s the lowest option.

Set the maximum value

As you might imagine, this is the opposite end of the spectrum, and is used to signify the highest value that users can select when tweaking their report.

You could in theory set this to 100% if you wanted to offer some kind of freemium model. Outside of SAAS though, you rarely see this, so think it through before you set the value and consider the business viability, as offering too hefty a discount might not be practical.

For example

We might cap the discount at 20%, translating to a value of 0.2.

Set the increment size

This value determines the size of the “steps” the parameter value takes, as a user adjusts it, between the minimum and maximum values.

A smaller value provides users with a finer degree of control over the exact parameter values being specified.

For example

For a somewhat more granular configuration, we can set the increment to 0.01, allowing users to increase the discount in steps of 1%.

Set the default ‘start point’

Set a starting point value that the parameter defaults to when the report is first loaded.

For example

For our loyalty program scenario, since the existing discount is 5%, a default value of 0.05 makes sense.

Displaying the parameter with a slicer

At this point, the parameter’s configuration is almost complete.

However, before we click “Create”, I want to draw your attention to one final option at the bottom of the dialog box - called a slicer.

Make sure to keep this option checked, so that you can then get a built-in slider control.

With the what-if parameter now connected to a slicer, your users will be able to slide through different parameter values, instantly seeing how these choices influence the visuals in the report. (More on this in a second).

How these new parameters affect your Power BI report

Before we get into the report itself and play around with the parameters, we need to understand what's happening.

So let’s take a quick look at how these parameters fit into the larger ecosystem of a Power BI report.

The new "Loyalty Member Discount" Table

Upon creating a "what-if" parameter, Power BI doesn't just adjust a single value in your data model. Instead, it spawns an entirely new table, named after the parameter we've defined—in our case, "Loyalty Member Discount."

This table holds crucial information about the parameter, and facilitates its integration with measures (and most importantly, visuals!) in the Power BI report it lives in.

To inspect this table, first navigate to the "Data" tab and look for a table with the name of your parameter:

Expanding this table, we’ll see two fields:

• “Loyalty Member Discount”, and
• “Loyalty Member Discount Value”

If we select the first field in this new table and then inspect its formula in the formula bar, we'll encounter a DAX function called GENERATESERIES:

Loyalty Member Discount = GENERATESERIES(0,0.2,0.01)

The GENERATESERIES function

Remember how I said that Power BI is incredibly user friendly?

Well, thanks to the dialog box information that we filled out, Power BI went ahead and wrote DAX code for this function, without us having to do anything technical!

This GENERATESERIES code function is what's creating the list of different parameter values we see when we drag the slider back and forth.

Sure, we could have written this ourselves with DAX code, but why not let Power BI do it for us?

The SELECTEDVALUE Function

When examining the second field in the parameter table, it reveals another DAX function, called SELECTEDVALUE.

Loyalty Member Discount Value = SELECTEDVALUE('Loyalty Member Discount'[Loyalty Member Discount], 0.05)

This function captures the current value from the slicer, allowing Power BI to fetch the exact parameter value the user has chosen from the GENERATESERIES function's output.

Why care?

Well, because this means that the measure that SELECTEDVALUE is populating here - “Loyalty Member Discount Value” - will ultimately be our ticket to incorporating that parameter value throughout our report.

tl;dr

The combination of these functions does the heavy lifting behind the scenes that makes dynamic parameters possible.

• GENERATESERIES creates the list of potential values, while
• SELECTEDVALUE pinpoints the exact one the user has selected

This system allows Power BI to weave the dynamic parameter into your data model, adjusting any calculations and visuals that use it accordingly.

How to add the User-Selected Parameter in DAX Formulas

Now that we've successfully set up the "what-if" parameter and explored its mechanics, it's time for the grand finale: integrating this dynamic value into our DAX formulas.

This will enable the visualizations that use those DAX formulas to respond to the changing scenarios in real-time.

Understanding the Revenue Measure

In order for the “Loyalty Member Discount” parameter to be of any practical use, it needs to be incorporated into a DAX measure.

This will then allow end-users to experiment with how different parameter values influence this measure and the metric it represents.

For example

In our particular scenario, the metric being affected by adjusting this parameter value will ultimately be revenue, since increasing or decreasing customer discounts will have an impact on the bottom line.

It’s important to understand the context in which your new parameter will be used, to be able to use it effectively.

Because we’re focusing on revenue and discount changes, let’s take a moment to walk through how revenue is currently being calculated without the benefit of such a parameter.

The formula is divided into two components:

• Loyalty member revenue, and
• Non-loyalty member revenue

Loyalty Member Revenue = CALCULATE(SUMX('Order Details','Order Details'[Unit Price]*'Order Details'[OrderQty]),Orders[LoyaltyMember]=1) * 0.95

Don’t worry if this looks a little complex, as you won’t even need to understand every detail of the DAX syntax you see here in order to incorporate our what-if parameter into this measure.

For now, all you need to understand is what's happening from a mile high perspective.

Part 1. Loyalty Member Revenue

In short, the current version of the “Loyalty Member Revenue” measure calculates unit price X order quantity, but only for orders placed by loyalty members, and then multiplies the result by 0.95.

((Unit price X Order quantity from loyalty members) X 0.95)

If you haven’t already guessed, this last piece is the key thing to focus on, as multiplying the loyalty member revenue by 0.95 is the same as applying a static, hard-coded 5% discount.

So let’s look at the other options.

Part 2. Non-Loyalty Member Revenue

Loyalty Member Revenue = CALCULATE(SUMX('Order Details','Order Details'[Unit Price]*'Order Details'[OrderQty]),Orders[LoyaltyMember]=0)

Non-loyalty member revenue is pretty much the same formula, except that it sums up ‘unit price X order quantity’ for orders placed by customers who are not loyalty members.

This means that because these customers are paying full price, the 0.95 multiplier doesn’t apply here.

Unit price X Order quantity (from non-loyalty member customers)

Putting it all together to find the revenue

Revenue = [Non-Loyalty Member Revenue] + [Loyalty Member Revenue]

Finally, the actual Revenue measure is simply the sum of “Loyalty Member Revenue” and “Non-Loyalty Member Revenue”.

Easy!

However, there is a slight issue in that our parameter is currently using a hard-coded 5% discount, but we can solve this…

Swapping in a new dynamic parameter

Instead of the static 5% discount that’s currently baked into the “Loyalty Member Revenue” measure, we want to utilize whatever discount percentage the user selects via our what-if parameters associated slicer.

More specifically, we’ll want to replace that hardcoded value of 0.95 with a formula that calculates the discount dynamically, which is (1 - Loyalty Member Discount Value).

If you remember, “Loyalty Member Discount Value” refers to the measure that we talked about earlier.

The logic behind this formula is simple: “Subtract the user-selected discount (as a decimal) from 1”.

This means that if the user selects a 5% discount, that translates to multiplying Loyalty Member Revenue by 0.95, but if they opt for a 20% discount, it results in a multiplication factor of 0.80, and so forth… You get the idea.

Understanding this, all that’s left to do is to modify the existing “Loyalty Member Revenue” measure, and swap out the hardcoded 0.95 with the dynamic formula from above.

This change will then ensure that the discount applied adjusts in real-time as users tweak the slicer.

Loyalty Member Revenue = CALCULATE(SUMX('Order Details','Order Details'[Unit Price]*'Order Details'[OrderQty]),Orders[LoyaltyMember]=1) * (1-'Loyalty Member Discount'[Loyalty Member Discount Value])

Testing our new Dynamic Integration

With our what-if parameter fully integrated into the “Revenue” measure, it’s finally time for the payoff and use it in a visualization!

For example

Here’s a simple Matrix visual broken out by food menu item categories at our fictional restaurant chain.

It features “Non-Loyalty Member Revenue”, “Loyalty Member Revenue”, and actual “Revenue” side-by-side.

Currently the discount for loyalty members is set to 5% (0.95).

So now, if we use the slider control on the slicer to adjust the loyalty member discount up a bit, from 0.5 to 0.15, we can see how that impacts sales.

Just like we’d expect, the “Loyalty Member Revenue” and “Revenue” numbers uniformly drop, while “Non-Loyalty Member Revenue” (to which the what-if parameter doesn’t apply) remains the same. Pretty darn cool!

Now obviously, we would need to also add in other data and information here before we make a business decision on if this discount works well for our business.

On paper, it seems like we’re making a loss, however, it could be that loyalty members make repeat purchases and have higher lifetime rates. So an initial loss of the sales is more than made up by the volume over time.

We could add all that into our reports, and then see the 3 month, 6 month and even lifetime impact that it would have, and then make decisions from there! It really is incredibly powerful.

How to add aesthetic adjustments

While functionality is vital, aesthetics matter too because they help us to visualize the data easier, and an easy to read report is one that gets read!

So what changes could we make?

Well, the default slicer currently displays values as decimals, but it might be more intuitive for users if these were formatted as percentages.

To achieve this, go ahead and navigate to the "Data" pane and select the ”Loyalty Member Discount” field (NOT the “Loyalty Member Discount Value”).

Then, use the formatting tools in the ribbon to adjust the data display format, changing it from decimal to percentage.

Easy!

Now, when users interact with the slicer, they'll see percentage values instead, making the experience more intuitive and aligned with the context of the data.

Start finding answers in your own data today!

We’ve only scratched the surface so far, but hopefully you can see just how much what-if parameters in Power BI can affect your data decisions.

These parameters give life to what used to be static reports, turning them into interactive playgrounds where we can experiment with different scenarios and see the results unfold right before our eyes. Now, instead of being confined to rigid data snapshots, we can explore a whole range of possibilities, making decisions that are informed and dynamic.

If you want to learn more about Power BI and how to use these features, check out my Power BI course.

The course will take you from absolute beginner in Power BI to being able to get hired in 2024 as a confident and effective Business Intelligence Analyst.

Bonus: By joining ZTM and taking my course, not only do you get access to the course content, but you can also ask me, and other students questions in our private Discord channel 24/7 - so you’ll never get stuck!