Power BI Parameters

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.read more” 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.”

Power-BI-Parameter

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI Parameters (wallstreetmojo.com)

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.

You can download this Power BI Parameters Excel Template here – Power BI Parameters Excel Template
Below is the data we are using for this example.
Power BI Parameters (Data)
  •  Upload the above data table to Power BI. Then, you can see the table as shown below.
Power BI Parameters (Sales Table)
  • First, we will create a summary table for each city. Drag the required Column to the value field.
Power BI Parameters (Table values)

We can view the table in the Report tab.

Power BI Parameters (Table)

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.”
Power BI Parameters (New Parameter)
  • Step 2: Now, we will see below What If Analysis parameter window.
Power BI Parameters (What-if Parameter)
  • 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.”
Power BI Parameters (Parameter Name)
  • Step 4: Next, we could choose the “Data Type” option as Whole Number, Decimal Number, and Fixed Decimal Number.”
Power BI Parameters (Data type Options)

I am choosing the data type as “Whole Number.”

Power BI Parameters (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.
Power BI Parameters (Min-Max)
  • 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.
Power BI Parameters (Increment)
  • Step 7: Now click on “Ok,” and we will have a slicer like the below one.
Power BI Parameters (Slicer)

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.”

Power BI Parameters (Discount Slab)

These two columns are auto-created by two DAX functions, i.e., “GENERATESERIES” and “SELECTEDVALUE.”

Discount Slab Table

Power BI Parameters (Discount Slab table)

Discount Slab Value table

Power BI Parameters (Discount Slab Value)

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.”
Power BI Parameters (New Measure)
  • This will ask us to name the new measure, so give the name as “Sales After Discount.”
Power BI Parameters (Sales After Discount)
  • Open SUMX function.
SUMX
  • 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.
SUMX Sales_Table

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.read more parameter.

  • So first, give reference to the “Total Sales” value column.
SUM Total Sales
  • Enter the minus sign (-).
SUMX Sales Minus
  • Now again, select the “Total Sales” column.
SUMX - Total Sales Column
  • Now put multiply sign (*).
SUMX Multiply
  • Now we need to multiply with “Discount Slab Value” from the “Discount Slab” parameter table.
Discount Slab -100

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.”
Created Measure
  • Now drag and drop new measures to the existing table to see new sales values after discount.
Sales After Discount table

It is showing the same values as the old one, isn’t it??

  • Now increase the discount slab to 3 and see numbers.
Power BI Parameters (Discount Slab range)

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.

You can download this Power BI Parameters Template here – Power BI Parameters Template

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 –

  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>