Forum Discussion
Ranking by three criteria
Hi PBI community,
I'm trying to rank a set of data (contracts), in ASCENDING order, according three criteria (columns):
- Year = 2011, 2012, , 2030
- Month = 1, 2, , 12
- Priority = 1, 2, 3, , 19
| Contract | Priority | Year | Month |
| A | 1 | 2013 | 1 |
| B | 13 | 2020 | 3 |
| C | 13 | 2016 | 3 |
| D | 5 | 2022 | 5 |
| E | 19 | 2018 | 12 |
| F | 3 | 2020 | 3 |
Desired output:
| Contract | Priority | Year | Month | Rank |
| A | 1 | 2013 | 1 | 1 |
| B | 13 | 2020 | 3 | 5 |
| C | 13 | 2016 | 3 | 2 |
| D | 5 | 2022 | 5 | 6 |
| E | 19 | 2018 | 12 | 3 |
| F | 3 | 2020 | 3 | 4 |
Should I use Rankx?
Tks in advance!
Yes Anonymous , but I would create a helper column. If in Power Query, create a custom column that is this:
= [Year] * 10000 + [Month] * 10 + [Priority]That would create a column with 20200313 for example for 2020, March, priority 13.
If you create a calculated column, it would be pretty much the exact same formula.
I generally recommend custom columns in Power Query, but here, I don't think it makes a difference.
Then use RANKX() using this column.
3 Replies
- edhans
Community Champion
Yes Anonymous , but I would create a helper column. If in Power Query, create a custom column that is this:
= [Year] * 10000 + [Month] * 10 + [Priority]That would create a column with 20200313 for example for 2020, March, priority 13.
If you create a calculated column, it would be pretty much the exact same formula.
I generally recommend custom columns in Power Query, but here, I don't think it makes a difference.
Then use RANKX() using this column.