Need Help in RANK DAX function (Dynamic Ranking)

[Imagauthamam]

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:

UserID	Region	Total Sales	Dynamic_Rank
1	North	2000	2
2	North	3000	1
3	North	3000	1
4	South	4000	1
5	South	4000	1
6	South	3500	2

Please let me know what am I missing in the RANK function

[Prashant_123]

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.

[Imagauthamam]

Hi @Kedar_Pande ,

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

[Kedar_Pande]

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

If this helped please Solution mark the original answer

[Kedar_Pande · Accepted solution]

@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