Forum Discussion

vibhoryadav23's avatar
vibhoryadav23
Icon for Helper II rankHelper II
2 years ago
Solved

Ranking on multiple columns

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
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Syk's avatar
    Syk
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

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

    B, Product 2 = 4

    ?

    • vibhoryadav23's avatar
      vibhoryadav23
      Icon for Helper II rankHelper II
      2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.