How to rank based on 2 columns?

[Kolumam]

Hi All,

I need to rank the table based on 2 columns. Here is the table.

Name	Probability	Quantity	Close Date
Tesla	20%	10	1/9/2020
Honda	50%	10	1/8/2020
Maruti	50%	10	1/7/2020

In my above table, first it needs to be ranked by probability * quanitity.

If probability * quantity is same for two rows, it needs to be ranked by the Close Date. So the earlier the close date, the higher the ranking.

Expected Output:

Name	Probability	Quantity	Close Date	Ranking
Tesla	20%	10	1/9/2020	3
Honda	50%	10	1/8/2020	2
Maruti	50%	10	1/7/2020	1

Is it possible?

[mahoneypat · Accepted solution]

Please try this expression.  I put your data in and called the table 'Make'.  It works by also adding in the days between today and the close date (divided by a large number so it doesn't affect the overall rank, but does break the tie).  Based on your values, you can increase the divided by number.

Rank with Date =
RANKX (
ALL ( Make ),
CALCULATE ( SUMX ( Make, Make[Quantity] * Make[Probability] ) )
+ DATEDIFF ( CALCULATE ( MIN ( Make[Close Date] ) ), TODAY (), DAY ) / 1000
)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mahoneypat · Accepted solution]

Please try this expression.  I put your data in and called the table 'Make'.  It works by also adding in the days between today and the close date (divided by a large number so it doesn't affect the overall rank, but does break the tie).  Based on your values, you can increase the divided by number.

Rank with Date =
RANKX (
ALL ( Make ),
CALCULATE ( SUMX ( Make, Make[Quantity] * Make[Probability] ) )
+ DATEDIFF ( CALCULATE ( MIN ( Make[Close Date] ) ), TODAY (), DAY ) / 1000
)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[az38]

Hi @Kolumam 

try technique from this great article https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

Column = RANKX('Table', [Probability]*[Quantity] + (10000 - INT([Close Date])/10000), , DESC)

[Kolumam]

Thank you so much. It kinda worked!

The issue now is how do I add a country filter from Country table?

See the below screenshot. If I filter a country, the ranking get's lost and it doesn't start from 1.

[mahoneypat]

Try changing the ALL() to an ALLSELECTED() so it responds to the slicers.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Kolumam]

Thanks mate! It worked. If I have any issues will get back to you.

[az38 · Accepted solution]

@Kolumam 

try a measure

Measure = RANKX(
ALLSELECTED('Table'), 
CALCULATE(
SUMX('Table','Table'[Probability]*'Table'[Quantity] + (10000 - INT('Table'[Close Date])/10000))
),
 , DESC)