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

      • 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"