Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
My goal is to take a group of scores, find the 75th percentile, then find the average of all score at/above the 75th percentile score. I will take this score and use it as a benchmark for other individual scores. The individual scores are not from a calculated column, they are from a measure which is constantly changed by a date slicer.
Expected output of what i want (refer to data at the end of the post for numbers)
ID: 2
Your Score Top Performers (I want this)
2.08 3.75
or
ID: 7
Your Score Top Performers (This will stay the same as before)
4.24 3.75
This is my measure to find the 75th percentile score:
ID | Score |
1 | 1.82 |
2 | 2.08 |
3 | 2.53 |
4 | 3.04 |
5 | 3.20 |
6 | 3.59 |
7 | 4.24 |
8 | 4.25 |
Solved! Go to Solution.
Hi @samwrite
For the upper quartile itself (if needed), your current measure is fine but could be made more succint:
ScoreUpperQ =
PERCENTILEX.INC (
ALL ( 'Table'[ID] ),
[Score],
0.75
)
For the average of Scores that are >= Upper Quartile, I would write a measure that constructs a table (similar to the measure you posted). This should be more efficient than referencing the ScoreUpperQ measure:
Average of Scores UpperQ+ =
VAR IDScore =
ADDCOLUMNS (
ALL ( 'Table'[ID] ),
"@Score", [Score]
)
VAR UpperQ =
PERCENTILEX.INC (
IDScore,
[@Score],
0.75
)
VAR Result =
AVERAGEX (
IDScore,
IF ( [@Score] >= UpperQ, [@Score] ) -- If < UpperQ then blank (ignored)
)
RETURN
Result
Regards,
Owen
You're welcome!
Just looking at your code there, I can't see a need for
Table2[Type] = SELECTEDVALUE(Table2[Type])
as this is applying a filter that already exists, assuming a single value of Table2[Type] in the filter context.
Also, removing the filter on Table1[ID] won't impact any filters on Table2[Type], so it looks like it's needed.
Can you try it with that piece of code removed, and do you get the same result?
Otherwise, post the full measures as I might be missing something.
Hi @OwenAuger
What I posted would be the full measure.
I can give more context for clarity, below are example tables
Table1
ID | Score |
1 | 1 |
2 | 2 |
3 | 3 |
4 | 4 |
5 | 5 |
6 | 6 |
7 | 7 |
8 | 8 |
Table2
ID | Type |
1 | A |
2 | A |
3 | A |
4 | A |
5 | B |
6 | B |
7 | B |
8 | B |
I would expect to see the Top Performers Average score relative for each Type
e.g.
ID: 2
Your Score Top Performers (relative to Type A scores)
2 3.75
or
ID: 7
Your Score Top Performers (changes to look at only Type B scores)
7 7.75
Ah right gotcha 🙂
It looks like Table2 is functioning as a dimension - is that right? Normally I would suggest that there should be a relationship between Table1[ID] and Table2[ID], and any filters on ID should be applied to columns of Table2.
Then in your Top Performers measure, as long as ALL() is only applied to the ID column (it should probably be Table2[ID] if that table is functioning as a dimension) any filters on Table2[Type] should be preserved.
One slight tweak you may want to make is to replace
ALL ( Table2[ID] )
with
CALCULATE (
VALUES ( Table2[ID] ),
ALL ( Table2[ID] )
)
This will ensure that the PERCENTILX.INC function iterates over values of ID with all ID filters removed, rather than all values that exist in the table.
Did that make sense - hoping I haven't confused things!
Regards,
Owen
Hi @samwrite
For the upper quartile itself (if needed), your current measure is fine but could be made more succint:
ScoreUpperQ =
PERCENTILEX.INC (
ALL ( 'Table'[ID] ),
[Score],
0.75
)
For the average of Scores that are >= Upper Quartile, I would write a measure that constructs a table (similar to the measure you posted). This should be more efficient than referencing the ScoreUpperQ measure:
Average of Scores UpperQ+ =
VAR IDScore =
ADDCOLUMNS (
ALL ( 'Table'[ID] ),
"@Score", [Score]
)
VAR UpperQ =
PERCENTILEX.INC (
IDScore,
[@Score],
0.75
)
VAR Result =
AVERAGEX (
IDScore,
IF ( [@Score] >= UpperQ, [@Score] ) -- If < UpperQ then blank (ignored)
)
RETURN
Result
Regards,
Owen
Hi @OwenAuger
This look like a nice solution. However I struggle with including my actual score in the equation. For some reason only existing measures pop up as valid choices when I try to adapt your solution to my data.
The column i wish to use is an integer that display duration in seconds for each row.
Any advice would be appreciated.
Best regards,
Fredrik
Hi Frederik,
You are only supposed to be able to reference existing measures. The "@Score" is simply a variable which will hold whatever measure you assign it.
-Sam
Hi @samwrite!
Thank you for the pointer. From the example I thought that I was supposed to declare unique id's and score coulmn for each row in the first part.
Unfortunately I still don't see how I can make this work on my current dataset, but I'll give it another go
Best regards,
Fredrik
Hi @OwenAuger , thanks for taking the time to help!
I tried out your measures with the addition of some filters i had excluded and it seems like I was able to get what I needed! Quick question regarding optimization, the reason why my first measure seemed complex was because i needed to include a selected value filter:
VAR IDScore =
calculatetable(SUMMARIZE(Table1, Table1[ID], "@Score", [Score]), ALL(Table1[ID]), Table2[Type] = SELECTEDVALUE(Table2[Type]))
Is there any way to further optimize this? Else everything else looks clean!
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
19 | |
15 |