Forum Discussion

Imagauthamam's avatar
Imagauthamam
Helper III
1 year ago
Solved

Need Help in RANK DAX function (Dynamic Ranking)

I have the following in which I have to do dynamic Ranking.
First, it should see the region and then based on Total sales
When the region changes, it should start again from 1, (Similar to partition by in SQL).

 


I'm also attaching the dax function which I tried:

Dynamic_Rank = RANK(DENSE,ALLSELECTED('Dynamic Rank'[Total Sales]),ORDERBY('Dynamic Rank'[Region],ASC,'Dynamic Rank'[Total Sales],DESC))
 
The correct output should be:

UserIDRegionTotal SalesDynamic_Rank
1North20002
2North30001
3North30001
4South40001
5South40001
6South35002

Please let me know what am I missing in the RANK function
Need Help
Show and Tell
Tips and Tricks
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    Imagauthamam 

    DAX formula:

    Dynamic_Rank = 
    RANKX(
    FILTER(
    'YourTableName', 
    'YourTableName'[Region] = EARLIER('YourTableName'[Region])
    ), 
    'YourTableName'[Total Sales], 
    , 
    DESC, 
    Dense
    )


    If this helped, a Kudos 👍 or Solution mark would be great!
    Cheers,
    Kedar Pande
    www.linkedin.com/in/kedar-pande

4 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    Imagauthamam 

    DAX formula:

    Dynamic_Rank = 
    RANKX(
    FILTER(
    'YourTableName', 
    'YourTableName'[Region] = EARLIER('YourTableName'[Region])
    ), 
    'YourTableName'[Total Sales], 
    , 
    DESC, 
    Dense
    )


    If this helped, a Kudos 👍 or Solution mark would be great!
    Cheers,
    Kedar Pande
    www.linkedin.com/in/kedar-pande

  • Imagauthamam's avatar
    Imagauthamam
    Helper III
    1 year ago

    Hi Kedar_Pande ,

    Thanks for the Response. This works.
    But is this not achievable through RANK function instead of RANKX function.


    • Kedar_Pande's avatar
      Kedar_Pande
      Super User
      1 year ago

      We have to use RANKX as we have to iterate in the table.

      If this helped please Solution mark the original answer

  • Prashant_123's avatar
    Prashant_123
    Helper I
    1 year ago

    Hi Everyone

     

    I want to Rank on basis of amount and also of new month starts then it should rank on basis of amount.

     

    Month Amount Rank

    Jan        234          1

    Jan        200          2

    Jan        100          3

    Feb         400         1

    Feb         149         2
     like this I want , as I have tried everything but it is not coming please help.