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!
I am trying to create a calculated column with a ranking for a values, but without any success.
The issue is that i need to create a separate ranking for each bucket (combination of unique values) within one table.
I was able to create a proper rating for entire table, but struggling with splittig that to a bucket.
The bucket for rating should be: ID, Qtr+Yr and Category.
So for each combination of unique ID, Qtr+Yr and Category value should be ranked separately, like below:
ID | Qtr+Yr | Category | Value | Rank |
123 | Q1 2020 | Cat 1 | 1000 | 3 |
123 | Q1 2020 | Cat 1 | 1500 | 2 |
123 | Q1 2020 | Cat 1 | 2000 | 1 |
ID | Qtr+Yr | Category | Value | Rank |
123 | Q1 2020 | Cat 2 | 2500 | 1 |
ID | Qtr+Yr | Category | Value | Rank |
123 | Q4 2019 | Cat 1 | 1000 | 3 |
123 | Q4 2019 | Cat 1 | 1400 | 2 |
123 | Q4 2019 | Cat 1 | 1500 | 1 |
ID | Qtr+Yr | Category | Value | Rank expected |
123 | Q4 2019 | Cat 2 | 2345 | 2 |
123 | Q4 2019 | Cat 2 | 3456 | 1 |
Below you have a high level example of my data :
ID | Qtr+Yr | Category | Value | Rank expected | Rank achieved |
123 | Q4 2019 | Cat 3 | 7777 | 1 | 4 |
123 | Q4 2019 | Cat 2 | 3456 | 1 | 7 |
123 | Q1 2020 | Cat 2 | 2500 | 1 | 8 |
123 | Q4 2019 | Cat 2 | 2345 | 2 | 9 |
123 | Q1 2020 | Cat 1 | 2000 | 1 | 10 |
123 | Q1 2020 | Cat 1 | 1500 | 2 | 11 |
123 | Q4 2019 | Cat 1 | 1500 | 1 | 12 |
123 | Q4 2019 | Cat 1 | 1400 | 2 | 13 |
123 | Q1 2020 | Cat 1 | 1000 | 3 | 16 |
123 | Q4 2019 | Cat 1 | 1000 | 3 | 17 |
345 | Q1 2020 | Cat 1 | 99999 | 1 | 1 |
345 | Q4 2019 | Cat 1 | 11111 | 1 | 2 |
345 | Q1 2020 | Cat 1 | 8888 | 2 | 3 |
345 | Q1 2020 | Cat 1 | 7777 | 3 | 5 |
345 | Q1 2020 | Cat 2 | 5555 | 1 | 6 |
345 | Q4 2019 | Cat 1 | 1113 | 2 | 14 |
345 | Q4 2019 | Cat 1 | 1112 | 3 | 15 |
345 | Q4 2019 | Cat 2 | 45 | 1 | 18 |
345 | Q4 2019 | Cat 2 | 22 | 2 | 19 |
345 | Q4 2019 | Cat 3 | 6 | 1 | 20 |
Any suggestions are appreciated!
Thanks!
Solved! Go to Solution.
For fun only,
DAX measure solution,
DAX calculated column solution,
PQ solution,
Excel worksheet formula is powerful enough,
SQL solution,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
For fun only,
DAX measure solution,
DAX calculated column solution,
PQ solution,
Excel worksheet formula is powerful enough,
SQL solution,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 |
---|---|
109 | |
99 | |
75 | |
63 | |
62 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
66 |