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
    )

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors