Percentile Calculation - from Individual evaluations to agent performance

[SUTChepe]

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 · Accepted solution]

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]

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]

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]

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]

Does switching the IF condition to

[Quartile] IN VALUES ( Quartiles[Filter] )

work more like you'd expect?

[SUTChepe]

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