Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

RANKX with two columns

Hi experts!

I have a sales table that shows me for each week the sales volume per article.

Year-Week Article Key 1 Key 2
2022-01 A 1 5
2022-02 A 2 2
2022-01 B 1 4
2022-02 B 2 5

 

Now I would like to get a Rank (calculated column) for each week based on Key 1 (primary) and Key 2 (secondary).

That means that all articles for each week should be ranked based on Key 1 first, and then based on Key 2.

I tried to do build first a total rank value using this:

Rank Total = 
Sales[Key1] + DIVIDE(Sales[Key2], 100)

 But this will not work since the range for both values is between 0 and 1.000.000.000

 

How would you do that?

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the attached file if it suits your requirement.

    One of ways to achieve this is, instead of using the amount directly from the column, try creating rank columns and then use those as a reference.

     

     

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi joshua1990 
    Please refer to attached sample file with the solution

    Rank = 
RANKX (
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Year-Week] ) ),
    'Table'[Key 1] * 1000000 + 'Table'[Key 2]
)
  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the attached file if it suits your requirement.

    One of ways to achieve this is, instead of using the amount directly from the column, try creating rank columns and then use those as a reference.