Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX calculated league table

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?

Need Help

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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!