Slicers based on percentage measures

[po]

We have a calculated measure which we have as a mesure as of format (numerator/denominator)*100 and various levels of hirerarchical reporting within the report.

Understand we cannot have slicer for a measure and calculated column not working for us.

Would like a slicer ideally to show good, bad and average

e.g Percent (70-100) = Good

     50-70 = Average

     < 50 = Bad

How best can this be achieved in power bi?

[edhans · Accepted solution]

Here is one way. See this file.

I created a calculated column in a grade table that used SWITCH:

Bucket = 
SWITCH(
    TRUE(),
    Grades[Score] >=70, 1,
    Grades[Score] >=50, 2,
    Grades[Score] < 50, 3
)

Then I created a simple table where 1 = Good, 2 = Average, 3 = Bad and ceated a relationship.

Then I dropped the Good/Average/Bad column into a slicer. Now your grades will be filtered by selecting good/bad/average.

[edhans · Accepted solution]

Here is one way. See this file.

I created a calculated column in a grade table that used SWITCH:

Bucket = 
SWITCH(
    TRUE(),
    Grades[Score] >=70, 1,
    Grades[Score] >=50, 2,
    Grades[Score] < 50, 3
)

Then I created a simple table where 1 = Good, 2 = Average, 3 = Bad and ceated a relationship.

Then I dropped the Good/Average/Bad column into a slicer. Now your grades will be filtered by selecting good/bad/average.

[po]

Thanks for suggestion.

What happens though if have a hirerachy 

e.g.

If have following scenario

Dimensions

Country

Region

Store

and want to assess sales performance at each level.  Sales Performance = qty sold/qty supplied  i.e. %age sales

Understand would be fine at lowest level if stored %age sales in table but if want the sales performance to be calculated dynamically at each level - would sales performance then not need to be a measure and cannot slice on measure?

Thanks  

[edhans · Accepted solution]

See my file again. I just added a Country and Country Average column to the grades. The country average is a calculated column and is:

Country Average = 
CALCULATE(
    AVERAGE(Grades[Score]),
    ALLEXCEPT(Grades,Grades[Country])
)

Then you just need to add a Bucket value for that using the same SWITCH logic.

You could add the region, city, store, whatever. It will just get a bit tedious.

[po]

Thanks for update