cancel
Showing results for
Did you mean:
Helper I

## Top Score Ranking (with Filter)

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?

1 ACCEPTED SOLUTION
Solution Sage

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_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)])``````

Solution Sage

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_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)])``````

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks