How to Create a What-if Parameter in Power BI — AI and Power BI — Chapter 7

You will learn how to Create a What-if Parameter in Power BI.


In any data analysis project, whenever we analyze data, always want to find out, some what-if scenarios. For example, what if sales increased by 10% or what if we decreased item cost by 1%. 

These what-if analyses can be very helpful for any decision-making process. 

Power BI has an AI-driven feature that enables us to implement these what-if scenarios. This is known as What-If Parameters

In this blog, we are going to explore this functionality with examples.

Get Data

For this case study, I consider the US Superstore dataset from Kaggle.

  • Let’s start with the Get Data option under the Home tab. As this is a CSV file, select the Text/CSV option from the drop-down list
  • Select the file named US Superstore data.csv
  • After selecting the file, data will be displayed in the below format
Image by Author
  • Click on Load and save data.

 What-if Parameter vs. Power Query Parameter

There is another parameter in Power Query, so please don’t confuse. What-if parameters are only available in the report and can be used in DAX calculations. Whereas Power Query parameters are available in Power Query Editor only. This parameter changes the behaviour of a query.

Properties of What-if Parameter

  1. Go to Modelling Tab → under the What if group, you can find New parameter
What if Parameter in Power BI
Image by Author

2. Click on New Parameter and below dialog box will open. 

What if Parameter in Power BI
Image by Author

3. It provides below information

  • Name of the what-if parameter
  • Data type (Whole number, Decimal number, Fixed decimal number)
  • Minimum
  • Maximum
  • Increment
  • Default: a value used for the what-if parameter when no value is selected in the slicer.
  • Add slicer to this page

Create Discount Range What if Parameter

  1. Create one What if parameter and provide the below information.
  2. Name -> “Discount Range”, Data type → Decimal number, Minimum → 0, Maximum -> 1, Increment -> 0.01
  3. Put Default as 0.01 and click the check box for “Add slicer to this page”.
What -if parameter in power bi
Image by Author

4. Click on the “OK” button and two things will be added.

a) Created “Discount Range” table

b) Added one slicer in the page as Discount Range.

What If Parameter in Power BI
Image by Author

What -if parameter created, but you don’t know how you can use this in your project. 

Let us find the application of this parameter. 

Application of What-If parameter

  1. Create one calculated measure to use this above parameter in your project.
  2. Create the below measure under the “US Superstore data” table.
    Revised Sales = ‘Discount Range’[Discount Range Value]*SUM(‘US Superstore data’[Sales])
what if parameter in Power BI
Image by Author

3. Click on Waterfall Chart under Visualization pane.

4. Add Order Date Hierarchy in the Category section. Keep the only Year and Month.

5. Add Category field in the Breakdown section.

6. Add Revised Sales in Values.

Waterfall chart in Power BI, What if parameter in Power BI
Image by Author

7. After creating any visualization, add some formatting to make it more presentable to the user. 

Waterfall chart in Power BI. What if parameter in Power BI
Image by Author

8. Use the Discount Range slicer to try different values and observe the changes in the chart. 

9. Don’t forget to click on the Save button.

Download

Please find the code in the below location

AI and Power BI — Chapter 7

Video

Conclusion

In this blog, we understand how to what-if parameter in Power BI.

In my next blog, we will learn more about AI and Power BI.

If you have any questions related to this project, please feel free to post your comments.

Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: