Calculate average per 20% bins based on ranking

[PowerBI_Account]

Hi all,

I've got the following data:

Customer Number	Score (1-100)	Ranking (as a measure)
1	95	1
2	22	6
3	36	5
4	57	3
5	11	8
6	2	9
7	88	2
8	47	4
9	15	7
10	1	10

I want to create a funnel graph with the average score per 20% buckets based upon ranks like so:

Bucket	Average Score
0-20%	91.5
21-40%	52
etc	etc

How would I go about doing this? I was thinking along the following lines, but it wont work:

Score 0-20% =
CALCULATE ( AVERAGE ( Customer[Score] ), [Ranking] < 0.2 * ALL ( [Ranking] ) )

Thanks in advance!

[PowerBI_Account]

I also tried the following, but the MAXX() just returns 1:

Score 0-20% =

var highestRank = CALCULATE(MAXX(Customer, [Ranking]), NOT(ISBLANK(Customer[Score])))

RETURN

CALCULATE(AVERAGE(Customer[Score]), [Ranking] < 0.2*highestRank))

[v-easonf-msft · Accepted solution]

Hi, @PowerBI_Account 

You can try measure as below:

Ranking = 
RANKX ( ALL ( Customer ), CALCULATE ( MAX ( Customer[Score] ) ),, DESC, DENSE )

M_Score 0-20% = 
VAR a =
    ROUNDDOWN ( 0.2 * MAXX ( ALL ( Customer ), [M_Ranking] ), 0 )
RETURN
    CALCULATE ( AVERAGE ( Customer[Score] ), FILTER ( Customer, [M_Ranking] <= a ) )

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[PowerBI_Account]

Thanks for the effort! Initial testing looks good, will test more in-depth later on in the week.