Parameters in Power BI Report
A parameter is nothing but having a different kind of scenarios to look at the final outcome of any of the project. Using parameter slicer in Power BI, we will be just looking at different kind of outcomes when the parameter values are increased or decreased. We hope you have done what-if-analysis in MS Excel. This is a useful analysis to look at different scenarios when the independent variable changes at certain incremental or decremented rates.
It is possible to do some kind of analysis in Power BI as well by using the “What-If-Analysis” parameter. In this article, we will show you how to create those parameters in Power BI. For example, assume you are calculating sales value and you want to know what would be the final sales numbers if you are giving different discount percentages. So this is possible with “What If Analysis Parameter”.
Example of Parameter in Power BI
Now we will see how we can use Parameter with some practical data. You can download the workbook used in this example to practice along with us from the below link.
- Upload the above data table to Power BI. Then you can see the table as shown below.
- First, we will create a summary table for each city. Drag the required Column to the value field.
We can view the table in the Report tab.
Now we need to create a parameter to see total sales if the discount given at different percentage values.
Follow the below steps to create a parameter slicer for a discount amount.
- Step 1: Go to the “Modeling” tab and click on “New Parameter”.
- Step 2: Now we will see below What If Analysis parameter window.
- Step 3: First thing we can do with this window is given a name to our parameter, we are giving the name as “Discount Slab”.
- Step 4: Next we could choose “Data Type” option as Whole Number, Decimal Number, and Fixed Decimal Number”.
I am choosing the data type as “Whole Number”.
- Step 5: Next we will decide what the minimum number is and what the maximum number is. In this case, our discount numbers are ranging from 0 to 5, so we will put “Zero” for minimum and 5 for maximum slabs.
- Step 6: Next option is “Incremental Value” i.e. when the slicer is increased or decreased what should be the incremental value, in this case, our numbers should increase by 1 and the default value is nothing.
- Step 7: Now click on “Ok” and we will have a slicer like the below one.
One more thing you need to notice is this slicer has an automatic table created and that table has two columns i.e. “Discount Slab and Discount Slab Value”.
These two columns are auto-created by two DAX functions i.e. “GENERATESERIES” and “SELECTEDVALUE”.
Discount Slab Table
Discount Slab Value table
GENERATESERIES function says “GENERATESERIES(0, 5, 1)” i.e. Minimum Value is “0”, Maximum value is “5” and Incremental value is by “1”.
Now using this table we will create new sales values for “Sales Table”.
- Right-click on the “Sales Table” and choose “New Measure”.
- This will ask us to name the new measure so give the name as “Sales After Discount”.
- Open SUMX function.
- The first argument of SUMX DAX function is “Table” i.e. from which table we are using columns to calculate the “Sales After Discount”, in this example we are concentrating on “Sales Table” so give reference of this table only.
Next argument is “Expression” i.e. what should be the calculation to conduct. So what we need to do here is from “Total Sales” value we need to deduct the percentage selected from the slicer parameter.
- So first give reference to “Total Sales” value column.
- Enter the minus sign (-).
- Now again select “Total Sales” column.
- Now put multiply sign (*).
- Now we need to multiply with “Discount Slab Value” from “Discount Slab” parameter table.
Note: Press ALT + ENTER to go to the next line in case of lengthy formulas.
Note: Divided by 100 to get the percentage value.
- Hit the enter key and we will have a new measure in “Sales Table”.
- Now drag and drop new measure to the existing table to see new sales values after discount.
It is showing the same values as the old one isn’t it??
- Now increase the discount slab to 3 and see numbers.
Wow!! Now we have new sales value after deducting 3% of the Total Sales values.
Note: Power BI Parameters in the report file can also be downloaded from the link below and the final output can be viewed.
Things to Remember Here
- In Power BI, Parameters are used to conduct what-if analysis.
- We can set the minimum and maximum parameters, data type numbers as well.
- When the parameter is set new table will be created by using DAX functions.
- New value needs to be created as “New Measure” not as “New Column”.
This has been a guide to Power BI Parameters. Here we discuss how to create and use parameters in a report using what-if parameters in power bi along with an example. You may learn more about Power BI from the following articles –