cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Super User

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 6
Super User

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"
)``````
Frequent Visitor

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?

Super User

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.

Frequent Visitor

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"

Super User

Does switching the IF condition to

`[Quartile] IN VALUES ( Quartiles[Filter] )`

work more like you'd expect?

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors