topic Re: Ranking on multiple columns in DAX Commands and Tips

Ranking on multiple columns

vibhoryadav23 — Tue, 27 Feb 2024 17:06:19 GMT

Hi all,

I want to create ranking in the data table (not in a visual) based on 2 categories (Column1,2) instead of 1 like in example below. Can someone help with the ranking formula to create a new ranking column instead of creating a new measure. Thanks in advance!

Column1	Sales	Rank
A	10	2
A	5	3
A	15	1
B	20	2
B	30	1
B	10	3

Re: Ranking on multiple columns

Syk — Tue, 27 Feb 2024 17:01:26 GMT

How are you wanting the ranking to work?
A, Product 1 = 1
A, Product 2 = 2
B, Product 1 = 3

B, Product 2 = 4

?

Re: Ranking on multiple columns

vibhoryadav23 — Tue, 27 Feb 2024 17:07:58 GMT

Sorry, the table posted was the wrong one. Updated the same. Column3, 'Rank' is the expected outcome based on first 2 columns. Thanks.

Re: Ranking on multiple columns

Anonymous — Wed, 28 Feb 2024 08:26:18 GMT

Hi vibhoryadav23 ,

1. If your third column is the result you expect, you can create a calculated column and use the rankx function to sort. For detailed information, please refer to the document: RANKX function (DAX) - DAX | Microsoft Learn.

Ranking = RANKX( FILTER( 'Table', 'Table'[Column1] = EARLIER('Table'[Column1]) ), 'Table'[Sales], , DESC, Dense )

2. If you want to sort the data in the second column according to size, you can place the fields in the table visual and adjust the sorting through the small triangle under the column name.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.