topic Re: Calculate Average of scores within top percentile (no calculated columns) in DAX Commands and Tips

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

Anonymous — Fri, 11 Feb 2022 22:12:34 GMT

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

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

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

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

OwenAuger — Fri, 11 Feb 2022 23:55:36 GMT

Hi Anonymous

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

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

Anonymous — Sat, 12 Feb 2022 02:03:21 GMT

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!

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

OwenAuger — Sat, 12 Feb 2022 10:43:49 GMT

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.

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

Anonymous — Mon, 14 Feb 2022 18:08:35 GMT

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

ID: 7

Your Score Top Performers (changes to look at only Type B scores)

7 7.75

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

OwenAuger — Tue, 15 Feb 2022 07:54:55 GMT

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

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

BooDaa — Fri, 10 Mar 2023 11:34:57 GMT

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

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

Anonymous — Fri, 10 Mar 2023 17:12:13 GMT

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

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

BooDaa — Sun, 12 Mar 2023 10:37:53 GMT

Hi Anonymous!
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