Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
try a measure
Measure = RANKX(
ALLSELECTED('Table'),
CALCULATE(
SUMX('Table','Table'[Probability]*'Table'[Quantity] + (10000 - INT('Table'[Close Date])/10000))
),
, DESC)
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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)
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks mate! It worked. If I have any issues will get back to you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |