Forum Discussion

SUTChepe's avatar
SUTChepe
Frequent Visitor
2 years ago
Solved

Percentile Calculation - from Individual evaluations to agent performance

Im trying to create a Quartile calculation for a Data coming from couple of thousand evaluations, the data comes 1 line per evaluation and I just do a simple average to calcualte the average score:

 

Score = AVERAGE(Append1[Evaluation Score])

Then I did percentiles 0.25, 0.5, 0.75 and 1 using formulas like this:

 

Q4 =
VAR _Table =
    SUMMARIZE ( Append1, Append1[Agent Number], "@Score", [Score] )
RETURN
    PERCENTILEX.INC( _Table, [@Score], 0.25)

But when I put this measure in my table it calculates the Q4 based on the line and not the overall, and used a formula to assign the agent quartile with this formula:

 

Agent Quartile = IF([Score]<[Q4],"Q4",IF([Score]<[Q3],"Q3",IF([Score]<[Q2],"Q2",IF([Score]<[Q1],"Q1","Check"))))

  

 
It seems the issue is coming from the Q1-Q4 calculations cause it calculates individually, If I add the Evaluation Number, the calcualtion is done based on the evaluation number.
 
If I remove all fields I just leave the Q1-Q4 it makes the correct calcualtion:
 

I made a Q4 Test trying to calculate this score at the selected table level and it works as long as I dont filter by form, by month, by Week or anything, If I apply any external filter the "Q4 Test" is Blank:

 

Q4 Test =
VAR __AllSelected = ALLSELECTED(Append1[Agent Number])
VAR __Table = ADDCOLUMNS(__AllSelected,"@Score",[Score])
VAR __Q4 = PERCENTILEX.INC(__Table,AVERAGE([@Score]),0.25)

RETURN

__Q4

 

 

 Sorry I'm unable to upload a file, I have every tool blocked by my admin to share a file.

  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    I suspect the problem is that [Q4] is being evaluated within the filter context of the Agent Number.

     

    Try removing the visual's filter context with ALLSELECTED:

    Q4 =
VAR _Table =
    CALCULATETABLE (
        SUMMARIZE ( Append1, Append1[Agent Number], "@Score", [Score] ),
        ALLSELECTED ()
    )
RETURN
    PERCENTILEX.INC ( _Table, [@Score], 0.25 )

     

    Side note: you can use a SWITCH instead of nested IFs:

    Agent Quartile =
SWITCH (
    TRUE (),
    [Score] < [Q4], "Q4",
    [Score] < [Q3], "Q3",
    [Score] < [Q2], "Q2",
    [Score] < [Q1], "Q1",
    "Check"
)

6 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    2 years ago

    I suspect the problem is that [Q4] is being evaluated within the filter context of the Agent Number.

     

    Try removing the visual's filter context with ALLSELECTED:

    Q4 =
VAR _Table =
    CALCULATETABLE (
        SUMMARIZE ( Append1, Append1[Agent Number], "@Score", [Score] ),
        ALLSELECTED ()
    )
RETURN
    PERCENTILEX.INC ( _Table, [@Score], 0.25 )

     

    Side note: you can use a SWITCH instead of nested IFs:

    Agent Quartile =
SWITCH (
    TRUE (),
    [Score] < [Q4], "Q4",
    [Score] < [Q3], "Q3",
    [Score] < [Q2], "Q2",
    [Score] < [Q1], "Q1",
    "Check"
)
    • SUTChepe's avatar
      SUTChepe
      Frequent Visitor
      2 years ago

      Worked perfectly 🙂 Thanks!
      Follow Up question tho, how do you think I can add the resulted Q1, Q2, Q3 and Q4 from the "Agent Quartile" as a Filter?

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        2 years ago

        Create a new model table (not related to any others) that has four rows, "Q1", "Q2", "Q3", "Q4". Use that table column as a slicer and create a new measure.

         

        QuartileFilter =
IF (
    SELECTEDVALUE ( QuartileSlicer[Quartile] ) = [Agent Quartile],
    1,
    0
)

         

        Use this by adding a measure filter [QuartileFilter] is 1 to your visual.