Top Score Ranking (with Filter)

[SO]

Hi All,

This is building on a solution from a different forum.  Although I am applying the concept to a very different context, I am building on a question asked by @seanmcc  and a solution by @v-kelly-msft shared here https://community.powerbi.com/t5/Desktop/Average-of-Top-N-values/m-p/1083398#M501682.  Sorry I didn't know how to reference.  

I'd like to show the top N marks for each player based on their highest "B" game score.  If they played the game twice and had two "B" games, the highest of these.  I will also need to slice these results based on a number of fields (e.g., Location or Server).   

Achievement Table   (Note, I placed an astrick * beside the highest to be considered)

Overall Results 

Result by Server 2  (if a field slicer was used)

Result by Location ZZZ  (if a field slicer was used)

First, our organization usese an analysis server, so table / column creation is limited to Measures / Dax.

If the same player happend to participate in the same game, we'd want to take the highest course (regardless if it was their first or second time)

Measure 1

#Maximum Scores =

var a = SUMX(GROUPBY('Achievement','Achievement'[PlayerID]), RANKX(ALLSELECTED('Achievement'),CALCULATE(SUM('Achievement'[Velocity])),,DESC,Dense))

return

IF(a<=3,MAX('Achievement'[Velocity]),0)

Measure 2

Average of top 3 = AVERAGEX(FILTER(ALL('Achievement'),'Achievement'[PlayerID]=MAX('Achievement'[PlayerID])&&'AchievementProfile'[#Maximum Scores] <>0),'AchievementProfile'[#Maximum Scores] )

I thought that I'd need to add something like:

FILTER('Achievement', Achievement[ReportPeriod] in {"1B", "2B", }

I tried placing the above in Measure 1 or Measure 2, but cannot resolve the errors. 

Also, as I can't create a seperate table to slice to different average counts, so I added the number "3" in IF(a<=3,MAX('Achievement'[Velocity]),0).  I thought about creating multiple measures, but any idea on how to recreate a table easily, that woudl be fantastic.  

Thoughts? 

[Anonymous · Accepted solution]

I have tried to write the measure to calculate the average of Top Scores of each player's B games. But the results are different from what you have shown.

This is the measure.

AveerageOfTopBGames = 
VAR CurrentPlayer = SELECTEDVALUE(Players[PlayerID])
VAR BTable = 
    FILTER(
        ALLSELECTED(Players),
        RIGHT(Players[Attempt],1)="B" &&
        Players[PlayerID]=CurrentPlayer)
VAR AC_BTable = ADDCOLUMNS(BTable,"Grouping",Players[PlayerID]&Players[Game])
VAR AC_BTableUnique =
    FILTER(
        AC_BTable,
        VAR CurGroup = [Grouping]
        RETURN
        [Maximum Velocity (m/s)]=MAXX(FILTER(AC_BTable,[Grouping]=CurGroup),[Maximum Velocity (m/s)])
    )
RETURN
AVERAGEX(AC_BTableUnique,[Maximum Velocity (m/s)])