Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
po
Post Prodigy
Post Prodigy

Slicers based on percentage measures

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?

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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  

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks for update

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.