DAX Slicer to show when metric is greater than the average of all

[elijah_gap]

Hello! 

I have a list of people with metrics that are performance indicators in a table. 

I want to create a slicer that when selected, makes it so only the people who are below the overall average in every metric shows up in the table. 

Thanks! 

[Anonymous · Accepted solution]

Hi @elijah_gap ,

Because you didn't provide the test data and didn't provide the results you expected, I can't guarantee that the method I'm providing is what you want to see. If I can't understand what you mean, please forgive me and provide the test data and expected results, thank you!

I'll start by showing you the test data that I created myself:

In order to give you a clearer view of the structure, I used the following two DAX functions to calculate the average of metricA and metricB:

Average_metricA = 
AVERAGEX(ALL('Table'),'Table'[metricA])

Average_metricB = 
AVERAGEX(ALL('Table'),'Table'[metricB])

Here's the slicer I created by using this table:

First, we configure filtering for metricA , you can use the following DAX to create a new measure:

Select_metricA = 
var _avgA=
AVERAGEX(
    ALL('Table'),'Table'[metricA])
return
SWITCH(
    TRUE(),
    MAX('Table 1'[METRIC])="metricA",
IF(
    MAX('Table'[metricA]) < _avgA,1,0)
)

Click on the test data table you created earlier, drag the new measure you just created into the Filters on this visual in Filters:

After you click the ‘Apply filter’, then choose the ‘metricA’ in the silcer, the final output is shown in the following figure:

All those whose metricA is less than the average of metricA are shown up.
Steps are the same for filtering metricB, but before you filter B, don't forget to make the criteria for filtering A clear!

MetricB's DAX is as follows:

Select_metricB = 
var _avgB=
AVERAGEX(
    ALL('Table'),'Table'[metricB])
return
SWITCH(
    TRUE(),
    MAX('Table 1'[METRIC])="metricB",
IF(
    MAX('Table'[metricB]) < _avgB,1,0)
)

The final output of metricB is shown in the following figure:

That's the solution for you. Finally, if I misunderstand what you mean, please forgive me and provide the test data and expected results, and I will continue to try to find a workaround for you.

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @elijah_gap ,

Because you didn't provide the test data and didn't provide the results you expected, I can't guarantee that the method I'm providing is what you want to see. If I can't understand what you mean, please forgive me and provide the test data and expected results, thank you!

I'll start by showing you the test data that I created myself:

In order to give you a clearer view of the structure, I used the following two DAX functions to calculate the average of metricA and metricB:

Average_metricA = 
AVERAGEX(ALL('Table'),'Table'[metricA])

Average_metricB = 
AVERAGEX(ALL('Table'),'Table'[metricB])

Here's the slicer I created by using this table:

First, we configure filtering for metricA , you can use the following DAX to create a new measure:

Select_metricA = 
var _avgA=
AVERAGEX(
    ALL('Table'),'Table'[metricA])
return
SWITCH(
    TRUE(),
    MAX('Table 1'[METRIC])="metricA",
IF(
    MAX('Table'[metricA]) < _avgA,1,0)
)

Click on the test data table you created earlier, drag the new measure you just created into the Filters on this visual in Filters:

After you click the ‘Apply filter’, then choose the ‘metricA’ in the silcer, the final output is shown in the following figure:

All those whose metricA is less than the average of metricA are shown up.
Steps are the same for filtering metricB, but before you filter B, don't forget to make the criteria for filtering A clear!

MetricB's DAX is as follows:

Select_metricB = 
var _avgB=
AVERAGEX(
    ALL('Table'),'Table'[metricB])
return
SWITCH(
    TRUE(),
    MAX('Table 1'[METRIC])="metricB",
IF(
    MAX('Table'[metricB]) < _avgB,1,0)
)

The final output of metricB is shown in the following figure:

That's the solution for you. Finally, if I misunderstand what you mean, please forgive me and provide the test data and expected results, and I will continue to try to find a workaround for you.

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elijah_gap · Accepted solution]

Exactly what I was looking for ! Thank you so much for the incredibly detailed explination!