Forum Discussion

jjones90274's avatar
jjones90274
Frequent Visitor
3 years ago

DAX RANKX by Quarter

I am having issues creating a RANKX function that ranks values that are in the same period. My data contains multiple cities with multiple years and quarters for each city (Ex: Dallas for 1Q 2021, 2Q 2021..../Phoenix for 1Q 2021, 2Q 2021...). For each period I would like to rank the cities with regards to a specific metric (i.e vacancy). I have tried to accomplish this both through adding a column and meaures but have been unable to make it work.

 

My current measure formula is: 

Test Vacancy Rank = RANKX(CALCULATETABLE(VALUES('Costar Data'[Vacancy Rate]),FILTER(ALLSELECTED('Costar Data'),'Costar Data'[End Of Quarter]=SELECTEDVALUE('Costar Data'[End Of Quarter]))),CALCULATE(SUM('Costar Data'[Vacancy Rate])))
 
I am less familuar with DAX so any help will be greatly appreciated.
 
Thanks!
DAX
Need Help
RANKX
RANKX by quarter

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    jjones90274 , Try measure like

     

    Rankx(allselected(Table[City]) , CALCULATE(SUM('Costar Data'[Vacancy Rate])), , desc,dense)

     

    Rankx(filter(allselected(Table[City], Table[Qtr Year]), [Qtr Year] =max(Table[Qtr Year])  , CALCULATE(SUM('Costar Data'[Vacancy Rate])), , desc,dense)

     

    For Rank Refer these links
    https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
    https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
    https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

    • jjones90274's avatar
      jjones90274
      Frequent Visitor
      3 years ago

      Thanks for the reply @amitchandak! I gave the first measure a try and I am still getting duplicate rankings (see below)

       

       

       For reference I have included a snapshot of the relationships I have in the model.

       

      The data I am using has both past dates with historical data and future dates with forcasted data. I tie the two data sources together through the calendar and market key tables. In my report I have slicers for Date (Year and Qtr) and Market.

       

    • jjones90274's avatar
      jjones90274
      Frequent Visitor
      3 years ago

      I also tried another method that got me close but it is still off.  It does not seem to be ranking properly and the ranking does not start at 1 (see below):

       

       

      The formula for GSVacancy Rank is: 

      GSVacancy Rank =
      CALCULATE(
          RANKX(ALL('GreenStreet-Data-2022-08-30'[Market]),[Measure - CoStar Vacancy],,ASC,Dense),
              ALL('GreenStreet-Data-2022-08-30'),
                  VALUES('GreenStreet-Data-2022-08-30'[Market]))