Change Measure from Average to Median (or any other computation) via Slicer

[Anonymous]

Hi Guys, I would like to know if there is a trick on changing the computation of dashboard from Average to Sum or Median to Sum via Slicer? 

Thank you

[Pragati11]

Hi @Anonymous ,

Yes you can do this in Power BI easily by using following steps:

1. Create a simple table in Power BI using Enter Data option for different calculation types you want: (modify these based on your requirement)

You will end up with following table:

Now suppose, in my scenario I want to modify the calculation type of Sales by Category.

I will start by creating a slicer by moving Calc Type column to it: (Make sure to make the slicer Single Select as logically only one type of calculation can be shown at a point)

Now I will create a measure something as below:

Calc_Type_Sales = 
var calcType = SELECTEDVALUE('Calculation Type'[Calc Type])
RETURN
SWITCH(
    TRUE(),
    calcType = "Sum", SUM(ClothingSales[Sales]),
    calcType = "Average", AVERAGE(ClothingSales[Sales]),
    MEDIAN(ClothingSales[Sales])
)

Now I will move this above measure against Category column in my data in a simple table visual:

Now start selecting the values from the slicer and the Calc_Type_Sales values changes for each category in the left table:

It shows:

• SUM of Sales when Sum is selected from the slicer
• AVERAGE of Sales when Average is selected from the slicer and so on

So the calculation type for Sales changes based on the selection I make in the slicer on the right.

You can modify this solution to your requirement by adding other calculation types as well.

Let me know how you get on with this solution.

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 
That is a wonderful idea however, is it possible to use it on a dashboard, for example:

This is the average count

and this is the median:

I want the dashboard itself to switch its computation using slicer. Apologies for the confusion

Thank you in advance.

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Yes use this slicer on the dashboard/report. Your chart calculations will change based on the selection in the slicer. It is the same logic, you will be using a chart visual in place of a simple table visual.

I just showed you on a table visual. Try it on a line chart visual, it will work.

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 

Its working! Thank you so much for your help!

[Anonymous]

Hi @Pragati11 
Its working!!!! 
Thank you so much for your help!