Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
samwrite
Frequent Visitor

Calculate Average of scores within top percentile (no calculated columns)

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:

ScoreUpperQ=
var summtable = calculatetable(SUMMARIZE(Table,Table[ID],"Score",[Score]),ALL(Table[ID]))
return calculate(PERCENTILEX.INC(summtable,[Score],0.75))
 
The reason i have ALL(Table[ID]) is to keep this value the same as I use different filters on the dashboard.
 
Below is the sample data I am working with. Thanks
IDScore
11.82
22.08
32.53
43.04
53.20
63.59
74.24
84.25
 
1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

8 REPLIES 8
OwenAuger
Super User
Super User

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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger 

What I posted would be the full measure.

 

I can give more context for clarity, below are example tables

Table1

IDScore
11
22
33
44
55
66
77
88

 

Table2

IDType
1A
2A
3A
4A
5B
6B
7B
8B

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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.

BooDaa_0-1678447922744.png

 

The column i wish to use is an integer that display duration in seconds for each row.

BooDaa_1-1678447971552.png


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.

BooDaa_0-1678617045613.png

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors