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! |
User | Count |
---|---|
143 | |
84 | |
64 | |
60 | |
57 |
User | Count |
---|---|
211 | |
109 | |
89 | |
76 | |
74 |