How to create a Measure based on Slicer in Power BI

In this Power BI Tutorial, we will discuss how to create a measure based on a slicer in Power BI. Apart from Power BI Measure based on Slicer, we will go through the below topics with examples.

  • Power BI DAX Measure based on Slicer
  • Power BI dynamic Measure based on slicer selection
  • Power BI Measure based on date slicer
  • Power BI filter Measure based on slicer

Power BI Measure based on Slicer

We can create a measure in Power BI based on a slicer. It is the same as creating a DAX measure based on a slicer.

Power Bi DAX measure based on slicer

We will create a measure in Power BI, that will show the value dynamically according to Slicer.

For example, we have used sample data based on Products and their financial report for our reference.

If you are new to Power BI, check out, how to create a report in Power BI.

Step-1:

Let’s create a measure to evaluate the total sales amount of the Products.

Total Sales = SUM(financials[ Sales])

Step-2:

Create a slicer with a products field like this:

power bi measure based on slicer
Power BI slicer

Step-3:

Insert a Card visual to visualize the total sales amount.

power bi measure based on slicer
power bi measure based on the slicer

This is showing the sum of the total sales amount of the products.

Step-4:

Now we can see the measures will dynamically change based on the selected option of the slicer like this:

power bi measure based on slicer
power bi measure based on the slicer

Also, it allows choosing multiple options. By selecting multiple options, it will add and visualize the sales of those selected options.

Power BI Measure based on Slicer
Power BI Measure based on Slicer

This is how a Power BI Measure changes value based on Slicer.

See also  How to create calculator in PowerApps

Read How to get selected value from Slicer in Power BI

Power BI dynamic Measure based on slicer selection

Now we will see how to create a Power BI Measure based on Slicer selection with a step-by-step guide.

Step-1:

First, we are going to create measures (Total sales, Total profit, Total unit sold) on Power BI Desktop under the Financial report.

We have already created one measure i.e. Total sales. Others are:

Total Profit = SUM(financials[Profit])  
Total Unit sold = SUM(financials[Units Sold])

Step-2:

Now we will create a table by using this measure’s value. As the slicer can be built on a column of the table.

To create a table on Power BI Desktop, go to modeling > new table > Insert below code on formula bar.

Slicer Table = {
    "Total Profit",
    "Total Sales",
    "Total Unit sold"
}
Power bi measure based on slicer selection
Power bi measure based on slicer selection

Step-3:

Using this table column, we will create a slicer on Power BI Desktop. But, as the table has no relationship with any other data it will not work.

Power bi measure based on slicer selection
Power bi measure based on slicer selection

Step-4:

Now we will create a measure to show the selected value of the slicer. By using a Card visual, we will visualize this measure.

Selected_Value = SELECTEDVALUE('Slicer Table'[Value])
Power bi measure based on slicer selection
Power bi measure based on slicer selection

It is showing which column is being selected. Otherwise, it will show Blank on the Card chart.

Step-5:

Again, we will create a measure that calculates the dynamic value of the selected column.

Dynamic value Calculation = 
SWITCH(
    TRUE(), 
    [Selected_Value] = "Total Sales", FORMAT([Total Sales],"0, 0.00"),
    [Selected_Value] = "Total Profit", FORMAT([Total Profit],"0,0.00"),
    [Selected_Value] = "Total Unit Sold", FORMAT([Total Unit sold],"0,0.00"),
    "Select a column"
    )

If we select “Total sales” from the slicer, it will return the total sales value on the card visual. Similarly, it will return the respective value as we select on the slicer. Otherwise, it will show a message to select a column on the slicer.

See also  How to Move files from one folder to another using Power Automate?

We can show this measured value by using a card chart.

Change measure dynamically using a slicer on Power BI
Change measure dynamically using a slicer on Power BI

This is showing the Total profit (as it is selected) on Card visual.

This is how a Power BI dynamic Measure based on slicer selection works.

Power BI Measure based on date slicer

Here, we will see how to measure shows values based on a date range slicer. For this, we need a date column in our data. We have already a date column in our sample data.

Create a slicer using the date column. It will create a date range slicer like below:

power bi measure based on date slicer
power bi measure based on date slicer

We will create a measure that will calculate the net sales of the products:

Net sales = [Total Sales] + [Total Profit]

Now create a card chart to show this measure. We can see according to the date range slicer, the value will be change dynamically on the card visual.

power bi measure based on date slicer
power bi measure based on date slicer

This is how a Power BI Measure based on date range Slicer works.

Power BI filter Measure based on slicer

Now we will see how to data get to filter by a measure in a Power BI Slicer with a step-by-step guide.

Step-1:

In Power BI Desktop, we have created a table chart using the date column (month, year) and Total sales (measure) like below:

power bi filter measure based on slicer
power bi filter measure based on the slicer

Step-2:

Now we will create a table having numerical values that will feed to our slicer.

Number filter = GENERATESERIES(0,[Total Sales],1000)
power bi filter measure based on slicer.
power bi filter measure based on the slicer

Step-3:

Now we will create a slicer by using this Number filter table. This will only show the value between 0 to total sales.

Filter measure based on Power BI slicer
Filter measure based on Power BI slicer

Step-4:

But we can see the slicer is not slicing anything. So we will create another measure to filter down the slicer.

Measure filter = 
var MinValue = MIN('Number filter'[Value])
var MaxValue = MAX('Number filter'[Value])
var CurrentValue = [Total sales]
RETURN
IF(
    CurrentValue >= MinValue && CurrentValue <= MaxValue,
    1,
    0
    )

Here, we are creating three variables as MinValue, MaxValue, and CurrentValue. If the CurrentValue is greater than or equal to MinValue and smaller than or equal to MaxValue then it will return 1 otherwise it will return 0.

See also  Remove Duplicate items from SharePoint List using Power Automate

For example, the total sales in 2013 August are 4484000(CurrentValue). But the selected ranges are 7787418 as MinValue and 44096572 as MaxValue.

According to our condition, the CurrentValue is less than MinValue. So it returns 0(Measure filter). Similarly, the others are return 0 or 1.

Power BI Filter by a measure in  slicer
Power BI Filter by a measure in the slicer

Step-5:

Now remove the Measure Filter from the table. Now we will add this measure on the filter panel of the Power BI Desktop. And, will apply filter.

Filter measure based on Power BI slicer
Filter measure based on Power BI slicer

This is how to do a Power BI filter Measure based on the slicer.

You may also like the following Power BI tutorials:

Conclusion

Here we learned Power BI Measure based on Slicer and the below topics:

  • How to create Power BI DAX/SUM Measure based on Slicer
  • How to create Power BI dynamic Measure based on slicer selection
  • How to create Power BI Measure based on date slicer
  • How to create Power BI filter Measure based on slicer

  • Bhawana,
    Nice article, can you add to the mix of dynamic value calculation another element like dynamic rows changed like Country and Product as additional Slicer table contain Products and Country so I can select dynamically between Values and (lets call it Filter Table) with out having to add buttons
    Thanks

  • >