RANKX with two columns

[joshua1990]

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?

[Jihwan_Kim · Accepted solution]

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.

[tamerj1]

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]
)