RANKX across multiple fields that aren't hierarchical categories

[Anonymous]

I am trying to apply a RANKX formula using a measure (but would consider a calculated column) for the benefit of a visual that contains two 'category' columns and a 'points' column. I've tried to follow guidance online, but I believe the key difference between my data and those guides online as that my two category columns have a many-to-many relationship whereas the online guides have a one to many relationship.

Here's an example of my data:

Looking at Player11 and Player13 they have multiple values (but only 1 value per season). What I require is for this data to be ranked directly from 1 to 10 (I can handle ties), the best I can achieve ranks each player within a season, but that's not what I want. Here's what I've tried:

(Measure) PtsRank= RANKX(ALL(Results[Player]),Results[Pts],,DESC,Skip)

(Measure )Pts Rank = RANKX(ALL(Results[Player]),CALCULATE([Pts]))

(Calc Column)Pts Rank = RANKX(Results,SUM(Results[Points]),,DESC,Skip)

Please note the measure Pts is as follows:

Pts = SUMX(Results,[Points])

The 2 measures only rank the player within each season, ending up wth the ranks for the visual above as 1,2,1,1,1,3,2,2 etc.)

The calculated column returns inaccurate reults altogether.

How can I create a measure that will result in the above visual being ranked from 1-10.

[v-yulgu-msft]

Hi @Anonymous ,

Rank =
RANKX(ALLSELECTED(Results),CALCULATE(SUM(Results[Pts])),,ASC,Dense)

Best regards,

Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft 

Further to my last message, I've got a bit closer. I created the following measure:

Rank2 = RANKX(ALLSELECTED(Results),CALCULATE(SUM(Results[Points]),ALLEXCEPT(Results,Results[Player],Seasons[Season])),,DESC,Dense)

This wasn't quite correct, but produced the following output:

I thought the problem may have been my page filter, so I added a filter to the measure that duplicates what my page filter does, as illustrated below, but this also produced the same results.

Rank2 = RANKX(FILTER(ALLSELECTED(Results),Results[TournamentType]<>"Grand Final"),CALCULATE(SUM(Results[Points]),ALLEXCEPT(Results,Results[Player],Seasons[Season])),,DESC,Dense)

[Anonymous · Accepted solution]

Actually the formula below was correct, there was another filter I wasn't accounting for:

Rank2 = RANKX(FILTER(ALLSELECTED(Results),Results[TournamentType]<>"Grand Final"),CALCULATE(SUM(Results[Points]),ALLEXCEPT(Results,Results[Player],Seasons[Season])),,DESC,Dense)

[Anonymous]

Hi @v-yulgu-msft 

Unfortunately that hasn't worked for me.

Here's a link to my file, and the screenshot below shows my output. AS Pts is a measure, I had to use the column (Points) from the Results table in the SUM statement (or I removed the SUM statement and just used Points, but neither worked): 

Rank = RANKX(ALLSELECTED(Results),CALCULATE(SUM(Results[Points])),,DESC,Skip)

https://www.dropbox.com/s/k93eke4yehn4inn/GENERIC.pbix?dl=0

It's on the page "Career inc 2013" and it's the visual in the bottom right of the page.