Forum Discussion
Helper IVRanking for separate category issue
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!
For fun only,
DAX measure solution,
DAX calculated column solution,
PQ solution,
Excel worksheet formula is powerful enough,
SQL solution,
1 Reply
CNENFRNLCommunity Champion
For fun only,
DAX measure solution,
DAX calculated column solution,
PQ solution,
Excel worksheet formula is powerful enough,
SQL solution,