Rank based on two columns

[joshua1990]

Hi experts!

I know, there have been a lot of posts regarding this, but they have not been useful for me.

The objective is to have a ranking based on two columns.

I have a dimension table (Table Dim) that lists all of our products.

Then I have a table (Table Trans) that some transactional values in different columns.

There is the first column called Column1 and another one called Column2.

Now I would like to build a ranking for each product based on Column2 as the first priority and Column1 as the second priority.

Using this DAX Measure I get the ranking for each column:

Rank1 =
    RANKX ( ALLSELECTED ( 'Table Dim'[Product] ), [Column1] )

Rank2 =
    RANKX ( ALLSELECTED ( 'Table Dim'[Product] ), [Column2] )

As a result, now I have the following:

Product	Rank1	Rank2	Rank Total
A1	1	0	4
A5	2	2	2
A3	3	1	1
A4	4	3	3
A2	5	0	5

The Rank Total column shows the result that I need.

How is this possible using DAX?

[Anonymous]

Hi @joshua1990 ,

Are your Rank1 and Rank2 calculated columns or measures? There are differences between the two, please refer to

Calculated Columns and Measures in DAX - SQLBI.

Please provide some sample data, which will help me create the correct formula.

For now, you can refer to this post to try to solve it.

Best Regards,

Stephen Tao

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

[AlexisOlson]

@joshua1990 New answers to this aren't likely to be much more useful than these older ones unless you can explain what you tried, what your code was, and what your result was. Then we can help fix it.

Otherwise, please see these:

https://community.powerbi.com/t5/Desktop/How-to-Rank-a-list-based-on-2-values-double-rankX/m-p/44008

https://community.powerbi.com/t5/Desktop/Ranking-based-on-multiple-criteria/m-p/892072

https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[smpa01]

@joshua1990  the logic for RankTotal is not clear. Please bare more details as to how you arrived there?

[joshua1990]

@smpa01: Thanks for your reply.

First, the Rank Total is based on the numerical value in Rank2. Then all remaining products are based on Rank1.

[smpa01]

@joshua1990  do I understand that if Rank2 =1 then that Product is Ranked 1 in Rank Total and rest of the products need to be Ranked again as per Rank1?

[amitchandak]

@joshua1990 , Need some data to suggest for column1 and column 2. If they are numbers 

Rank1 =
    RANKX ( ALLSELECTED ( 'Table Dim'[Product] ), [Column1]+ [Column2] )


Rank1 =
    RANKX ( ALLSELECTED ( 'Table Dim'[Product] ),calculate( [Column1]+ [Column2] ) )