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: ...
  • 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"
)
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.

  • SUTChepe's avatar
    SUTChepe
    Frequent Visitor
    2 years ago

    Hey Alexis! I created the table and used the formula but the result, without selecting anything in the filter it comes up as "Q3"


    This is the formula

     

    Where Quartiles[Filter] is 


    The SWITCH formula above calculates the overall Score as a Q3 and then it is always "Selected in Q3"

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

      Does switching the IF condition to

      [Quartile] IN VALUES ( Quartiles[Filter] )

      work more like you'd expect?

      • SUTChepe's avatar
        SUTChepe
        Frequent Visitor
        2 years ago

        I think the issue comes from the SWITCH Calculation cause it grabs the Total for all selected agents and categorize it as "Q3" cause the average score for all agents is in the Q3

        the change in formula shows TRUE all the time for Q3 even if I select, Im playing with the filter and formula to see what I can do