cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SO
Helper I
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)

 

SO_0-1617743541225.png

 

 

Overall Results 

SO_0-1617743665389.png

 

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

SO_1-1617743750311.png

 

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

SO_2-1617743821366.png

 

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
sreenathv
Solution Sage
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.

 

sreenathv_0-1617774831095.png

 

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

 

 

 

 

View solution in original post

1 REPLY 1
sreenathv
Solution Sage
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.

 

sreenathv_0-1617774831095.png

 

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

 

 

 

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors