DAX calculated league table

[Anonymous]

Hi all

I am trying to calculate a table with DAX where locations are ranked based on a deviation from a target (TOTAL DEV) for each month. The first 5 ranks get points every month. It becomes some sort of a game who got the most points. In order to move back and forth in history, what I need is a table that has:

IdMonth
IdSite
SiteLabel
TOTAL DEV
Rank
Points (which will be a calculated column)

All locations would get a rank for each month.

I created a Measure SiteRank:

SiteRank = CALCULATE(RANKX(ALLSELECTED('Location'[SiteLabel]);[Total Deviation]))

And the  Score_Table is the calculated table:

Score_Table = ADDCOLUMNS(
SUMMARIZE('Location';'PowerBI_DCMAdmin ALL_Dim_Location'[IdSite];'Location'[SiteLabel]);"TOTAL DEV";[Total Deviation];"Rank";'Measures'[SiteRank])

The problem I face is that the two tables Location and Date_Day (where I can find IdMonth and is my central date table for the model) have no direct relationship. It all flows through the fact tables (from Location to Fact and from Date to Fact) thus I can't use it in the summarize statement.


I hope I was able to describe the problem sufficiently.
Can anyone help?

[v-chuncz-msft]

@Anonymous 

Instead, you may try using SUMMARIZECOLUMNS.

[Anonymous]

Awesome, thanks @v-chuncz-msft now the table works! But I created anew problem with it: the ranking does not work.
in the report itself the measure SiteRank works just fine when I select a month to show the score rankings etc. But if i add it to the calculated table using... 

Score_Table = SUMMARIZECOLUMNS(DATE_Day'[IdMonth];'Location'[IdSite];'Location'[SiteLabel];"TOTAL DEV";[Total Deviation];"Rank";[SiteRank])

 
the rank no longer works, it just shows rank one and rank 58 (the last one):
 

something wrong in my measure?

SiteRank = RANKX(ALLSELECTED('Location'[SiteLabel]);[Total Deviation])

Thanks again!