Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSee 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHere 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks for update
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |