Generate rank based on 2 columns(Department and Names ) and 1 measure(Total Sales) in Dax

[Vijay123]

[v-sshirivolu]

Hi @Vijay123 ,
Thanks for reaching out to the Microsoft fabric community forum.

Create the Rank Column

Add a calculated column to assign the rank within each department :

Rank by Department =
RANKX(
FILTER(
YourTableName,
YourTableName[Department] = EARLIER(YourTableName[Department])
),
YourTableName[Total Sales],
,
DESC,
DENSE
)

 
Please find the attached .pbix file for your reference. 

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team

[v-sshirivolu]

Hi @Vijay123 ,
Just wanted to check if you had the opportunity to review the suggestion provided? If so, please mark the helpful reply. This will be helpful for other community members who have similar problems to solve it faster.

[v-sshirivolu]

Hi @Vijay123 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

[v-sshirivolu]

Hi @Vijay123 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[ajaybabuinturi · Accepted solution]

Hi @Vijay123,
Wanted to let you know that attched image is doesn't get any clarity information about your reporting needs. However I am providing a DAX logic for you, could you please try these options. You can refer how these DAX logics will work in below image. Let me know if you have any questions.

//Option1
RankByNames = RANKX(CROSSJOIN(ALL(Table[Department]),ALL(Table[Names])),[Total Sales],,DESC) //The Rank will be constant as we are restricting filters by using ALL

//Option2
RankByNames = RANKX(CROSSJOIN(ALL(Table[Department]),ALLSELECTED(Table[Names])),[Total Sales],,DESC) //It will work for even if you have slicer for Names.

ReferenceImage:

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[bhanu_gautam]

@Vijay123 , Try using

Rank =
RANKX(
FILTER(
ALL('SalesTable'),
'SalesTable'[Department] = EARLIER('SalesTable'[Department]) &&
'SalesTable'[Names] = EARLIER('SalesTable'[Names])
),
'SalesTable'[Total Sales],
,
DESC,
DENSE
)