Forum Discussion
RANK By Multiple Criteria
I am trying to calculate a National Rank and State Rank using the sample data set below. National Rank would rank Revenue by Client and Product. State Rank would rank Revenue by Client and Product within the State. I am looking to replicate the 4 columns to the right in Power BI using DAX. Any help would be greatly appreciated.
| High-To-Low | Low-To-High | High-To-Low | Low-To-High | ||||||
| Client | State | Product | Volume | Price | Revenue | National Rank | National Rank | State Rank | State Rank |
| A | TX | 1 | 4503 | $23 | $103,569 | 4 | 3 | 3 | 1 |
| A | TX | 2 | 3696 | $98 | $362,208 | 4 | 3 | 1 | 3 |
| A | TX | 3 | 8350 | $50 | $417,500 | 2 | 5 | 2 | 2 |
| A | TX | 4 | 674 | $91 | $61,334 | 5 | 2 | 3 | 1 |
| A | TX | 5 | 8497 | $20 | $169,940 | 1 | 1 | 1 | 3 |
| B | CA | 1 | 2185 | $23 | $50,255 | 5 | 2 | 2 | 2 |
| B | CA | 2 | 6522 | $98 | $639,156 | 3 | 4 | 3 | 1 |
| B | CA | 3 | 1393 | $50 | $69,650 | 5 | 2 | 2 | 2 |
| B | CA | 4 | 452 | $91 | $41,132 | 6 | 1 | 3 | 1 |
| B | CA | 5 | 8216 | $20 | $164,320 | 2 | 5 | 1 | 3 |
| C | TX | 1 | 8861 | $23 | $203,803 | 2 | 5 | 1 | 3 |
| C | TX | 2 | 2650 | $98 | $259,700 | 5 | 2 | 2 | 2 |
| C | TX | 3 | 8725 | $50 | $436,250 | 1 | 6 | 1 | 3 |
| C | TX | 4 | 6441 | $91 | $586,131 | 1 | 6 | 1 | 3 |
| C | TX | 5 | 5852 | $20 | $117,040 | 4 | 3 | 2 | 2 |
| D | CA | 1 | 9240 | $23 | $212,520 | 1 | 6 | 1 | 3 |
| D | CA | 2 | 7179 | $98 | $703,542 | 2 | 5 | 2 | 2 |
| D | CA | 3 | 7469 | $50 | $373,450 | 3 | 4 | 1 | 3 |
| D | CA | 4 | 3464 | $91 | $315,224 | 2 | 5 | 1 | 3 |
| D | CA | 5 | 6463 | $20 | $129,260 | 3 | 4 | 2 | 2 |
| E | TX | 1 | 7620 | $23 | $175,260 | 3 | 4 | 2 | 2 |
| E | TX | 2 | 330 | $98 | $32,340 | 6 | 1 | 3 | 1 |
| E | TX | 3 | 7402 | $50 | $370,100 | 4 | 3 | 3 | 1 |
| E | TX | 4 | 1805 | $91 | $164,255 | 4 | 3 | 2 | 2 |
| E | TX | 5 | 1643 | $20 | $32,860 | 6 | 1 | 3 | 1 |
| F | CA | 1 | 1312 | $23 | $30,176 | 6 | 1 | 3 | 1 |
| F | CA | 2 | 7684 | $98 | $753,032 | 1 | 6 | 1 | 3 |
| F | CA | 3 | 1349 | $50 | $67,450 | 6 | 1 | 3 | 1 |
| F | CA | 4 | 2724 | $91 | $247,884 | 3 | 4 | 2 | 2 |
| F | CA | 5 | 5109 | $20 | $102,180 | 5 | 2 | 3 | 1 |
Hi bprokop
I created a sample with the data you provided .
Create 4 measures separately and rank them according to different categories.
National high-low = RANKX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product])),CALCULATE(MAX('Table'[Revenue])),,DESC,Dense)National low-high = RANKX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product])),CALCULATE(MAX('Table'[Revenue])),,ASC,Dense)State high-low = RANKX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product]) && 'Table'[State]=MAX('Table'[State])),CALCULATE(MAX('Table'[Revenue])),,DESC,Dense)State low-high = RANKX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product]) && 'Table'[State]=MAX('Table'[State])),CALCULATE(MAX('Table'[Revenue])),,ASC,Dense)The final result is as shown :
I have attached my pbix file , you can refer to it .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi bprokop
I created a sample with the data you provided .
Create 4 measures separately and rank them according to different categories.
National high-low = RANKX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product])),CALCULATE(MAX('Table'[Revenue])),,DESC,Dense)National low-high = RANKX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product])),CALCULATE(MAX('Table'[Revenue])),,ASC,Dense)State high-low = RANKX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product]) && 'Table'[State]=MAX('Table'[State])),CALCULATE(MAX('Table'[Revenue])),,DESC,Dense)State low-high = RANKX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product]) && 'Table'[State]=MAX('Table'[State])),CALCULATE(MAX('Table'[Revenue])),,ASC,Dense)The final result is as shown :
I have attached my pbix file , you can refer to it .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.