cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Douttful
Frequent Visitor

Requesting help writing a measure to return a text label for an averaged score.

Hello! 

 

I'm building a power BI report that will show how analysts in my call center are doing per period and YTD. Analysts are scored on a 5 point range for all their Metrics. This is an old default from the department. Originally the 5 scale number would show how people were doing. 

SCALE: 

5 = Outstanding

4= Exceeds Expecations

3= Meets Expecations

2 = Needs Improvement

1= Unacceptable.

 

The metric I'm working on right now is Quality Assurance, we're grading submitted issue tickets. These tickets are graded on a 3 point scale then averaged per period and YTD. 

 

Grading scale: 

5 = Exceeds

3 = Meets

1 = Needs Improvement 

 

I've got the QA Average built into a measure that is working without issues. NOW what I'm trying to do is to create a measure that will take the average ticket score number, reference to a label table where the "Metric Number" range is and then return the text label for that value. Below is a screenshot of my report. 

Douttful_4-1635706466499.png

 

 

The Card visuals to the right will HOPEFULLY display the grade. For these numbers is should be displaying 

 

Douttful_3-1635706440082.png

 

 

I know I'm SO CLOSE with this Measure: 

 

QA Metric = CALCULATE(SELECTEDVALUE('Score-Quality Assurance'[Quality Assurance]), FILTER(ALL('Score-Quality Assurance'[Quality Assurance]), MAX('Score-Quality Assurance'[QA Score Min]) <= [TK Score Average]&& MAX('Score-Quality Assurance'[QA Score Max])>=[TK Score Average])) 
 
But it's only returning when Outstanding is the Label. 
 
Here are my data tables: 
 
Score-Quality Assurance Table: 

Douttful_1-1635703012189.png

 

QA Metric # is archaic and used as a label.

QA Grading is the actual numerical score being used to calculate the average.

QA Score Min and QA Score Max show the range to reach the different Quality Assurance Labels. For example if someones average score is a 3 it should return the value Meets Expectations. 

 

MEASURE: 

TK Score Average = AVERAGE('QA SurveyData_LIVE'[Ticket Score])
The above is working perfectly.  Fake Ticket Score table below. 
 
Analyst

Ticket Score

Darth Vader5

Darth Vader

3
Darth Vader3
Darth Vader1
Darth Vader3
Darth Vader3
 
I hope this is enough data. I don't know how much of the background is helpful or not. 
 
Thanks so much for anyone's help! 

 

 
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Try this:

 

QA Metric =
VAR AvgScore = [TK Score Average]
VAR MinScore =
    CALCULATE (
        MAX ( 'Score-Quality Assurance'[QA Score Min] ),
        'Score-Quality Assurance'[QA Score Min] <= AvgScore
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Score-Quality Assurance'[Quality Assurance] ),
        'Score-Quality Assurance'[QA Score Min] = MinScore
    )

 

This finds the maximal [QA Score Min] and then looks up [Quality Assurance] associated with that value.

 

Or this:

QA Metric =
VAR AvgScore = [TK Score Average]
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Score-Quality Assurance'[Quality Assurance] ),
        'Score-Quality Assurance'[QA Score Min] <= AvgScore,
        'Score-Quality Assurance'[QA Score Max] >= AvgScore
    )

View solution in original post

2 REPLIES 2
Douttful
Frequent Visitor

That worked THANK YOU SO MUCH!!!

AlexisOlson
Super User
Super User

Try this:

 

QA Metric =
VAR AvgScore = [TK Score Average]
VAR MinScore =
    CALCULATE (
        MAX ( 'Score-Quality Assurance'[QA Score Min] ),
        'Score-Quality Assurance'[QA Score Min] <= AvgScore
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Score-Quality Assurance'[Quality Assurance] ),
        'Score-Quality Assurance'[QA Score Min] = MinScore
    )

 

This finds the maximal [QA Score Min] and then looks up [Quality Assurance] associated with that value.

 

Or this:

QA Metric =
VAR AvgScore = [TK Score Average]
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Score-Quality Assurance'[Quality Assurance] ),
        'Score-Quality Assurance'[QA Score Min] <= AvgScore,
        'Score-Quality Assurance'[QA Score Max] >= AvgScore
    )

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors