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 kinds 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-AnalysisWhat-If-AnalysisWhat-If Analysis in Excel is a tool for creating various models, scenarios, and data tables. It enables one to examine how a change in values influences the outcomes in the sheet. The three components of What-If analysis are Scenario Manager, Goal Seek in Excel, and Data Table in Excel.” 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 is 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 the “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 to this table only.
The next argument is “Expression,” i.e., what should be the calculation to conduct. So what we need to do here is from the “Total Sales” value we need to deduct the percentage selected from the slicerSlicerSlicers are a handy feature in excel to use multiple auto filters in a data table. However, it involves many clicks to use a filter on every column to find a date. A slicer makes it easier as it can be done with a few clicks. parameter.
- So first, give reference to the “Total Sales” value column.
- Enter the minus sign (-).
- Now again, select the “Total Sales” column.
- Now put multiply sign (*).
- Now we need to multiply with “Discount Slab Value” from the “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 measures 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 a 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, a 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 –