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.
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
- 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
- Go to Modelling Tab → under the What if group, you can find New parameter
2. Click on New Parameter and below dialog box will open.
3. It provides below information
- Name of the what-if parameter
- Data type (Whole number, Decimal number, Fixed decimal number)
- 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
- Create one What if parameter and provide the below information.
- Name -> “Discount Range”, Data type → Decimal number, Minimum → 0, Maximum -> 1, Increment -> 0.01
- Put Default as 0.01 and click the check box for “Add slicer to this page”.
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 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
- Create one calculated measure to use this above parameter in your project.
- Create the below measure under the “US Superstore data” table.
Revised Sales = ‘Discount Range’[Discount Range Value]*SUM(‘US Superstore data’[Sales])
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.
7. After creating any visualization, add some formatting to make it more presentable to the user.
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.
Please find the code in the below location
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.