Forum Discussion
Solved
Advanced Ranking (Rankx)
Hi, I am struggling with ranking measures in Power BI. I have visual sorted descening by Number of Points (field Current) and I want to rank all filtered Analysts by this measure. I cannot shar...
This worked with the sample I set up based on what you provided.
Selected Analyst Rank = VAR _analystCurrent = CALCULATETABLE( SUMMARIZECOLUMNS( 'Sample'[Analyst], "currentSum", [Current] ), ALLSELECTED( 'Sample' ) ) VAR _analystCurrentRank = RANK( _analystCurrent, ORDERBY( [currentSum], DESC ) ) RETURN _analystCurrentRankFor visibility, sample semantic model and data I tested. Given that [Current] is a measure, I used the following sample data to more fully test the above:
Current = SUM( SampleData[Current Vals] )Sample (as shown)
Perfect Rank Rank (Current) Analyst Corp Rank Team Raw Weighted 1 1 Ann a Sales 445 4666 2 2 Tom a Finance 555 4000 3 3 Henrik a HR 56 3600 4 4 John a HR 456 3580 5 1 Johanna b Sales 666 3000 6 2 Robert b Sales 666 2900 7 3 Sarah b Sales 567 2899 8 4 Joy b Sales 7888 2801 9 5 Patrick b Sales 788 2800 10 6 Steven b Sales 788 2780 11 1 Alicia c Finance 7858 2705 12 2 Carol c Finance 7888 2700 13 3 Ursula c Finance 7888 2690 14 4 Adrian c Finance 888 2600 15 5 Milos c Finance 88885 2560 16 1 Dorothy d Account 4 2501 17 2 Tanja d HR 447 2500 18 3 Chris d HR 788 2407 19 4 Dominika d HR 448 2406 20 5 Nicola d HR 4888 1000 SampleData
Perfect Rank Current Vals 1 4346.291343 1 319.7086567 2 2465.796833 2 1534.203167 3 1583.478001 3 2016.521999 4 644.1608684 4 2935.839132 5 2442.290728 5 557.7092718 6 1545.220047 6 1354.779953 7 1125.799336 7 1773.200664 8 1602.011446 8 1198.988554 9 297.5253945 9 2502.474606 10 37.11925691 10 2742.880743 11 650.5460141 11 2054.453986 12 1057.462128 12 1642.537872 13 769.252108 13 1920.747892 14 377.7934778 14 2222.206522 15 2340.171915 15 219.8280848 16 456.5210337 16 2044.478966 17 1425.995879 17 1074.004121 18 17.09018432 18 2389.909816 19 728.0278296 19 1677.97217 20 710.2578621 20 289.7421379
This worked with the sample I set up based on what you provided.
Selected Analyst Rank =
VAR _analystCurrent =
CALCULATETABLE(
SUMMARIZECOLUMNS( 'Sample'[Analyst], "currentSum", [Current] ),
ALLSELECTED( 'Sample' )
)
VAR _analystCurrentRank = RANK( _analystCurrent, ORDERBY( [currentSum], DESC ) )
RETURN
_analystCurrentRank
For visibility, sample semantic model and data I tested. Given that [Current] is a measure, I used the following sample data to more fully test the above:
Current = SUM( SampleData[Current Vals] )
Sample (as shown)
| Perfect Rank | Rank (Current) | Analyst | Corp Rank | Team | Raw | Weighted |
| 1 | 1 | Ann | a | Sales | 445 | 4666 |
| 2 | 2 | Tom | a | Finance | 555 | 4000 |
| 3 | 3 | Henrik | a | HR | 56 | 3600 |
| 4 | 4 | John | a | HR | 456 | 3580 |
| 5 | 1 | Johanna | b | Sales | 666 | 3000 |
| 6 | 2 | Robert | b | Sales | 666 | 2900 |
| 7 | 3 | Sarah | b | Sales | 567 | 2899 |
| 8 | 4 | Joy | b | Sales | 7888 | 2801 |
| 9 | 5 | Patrick | b | Sales | 788 | 2800 |
| 10 | 6 | Steven | b | Sales | 788 | 2780 |
| 11 | 1 | Alicia | c | Finance | 7858 | 2705 |
| 12 | 2 | Carol | c | Finance | 7888 | 2700 |
| 13 | 3 | Ursula | c | Finance | 7888 | 2690 |
| 14 | 4 | Adrian | c | Finance | 888 | 2600 |
| 15 | 5 | Milos | c | Finance | 88885 | 2560 |
| 16 | 1 | Dorothy | d | Account | 4 | 2501 |
| 17 | 2 | Tanja | d | HR | 447 | 2500 |
| 18 | 3 | Chris | d | HR | 788 | 2407 |
| 19 | 4 | Dominika | d | HR | 448 | 2406 |
| 20 | 5 | Nicola | d | HR | 4888 | 1000 |
SampleData
| Perfect Rank | Current Vals |
| 1 | 4346.291343 |
| 1 | 319.7086567 |
| 2 | 2465.796833 |
| 2 | 1534.203167 |
| 3 | 1583.478001 |
| 3 | 2016.521999 |
| 4 | 644.1608684 |
| 4 | 2935.839132 |
| 5 | 2442.290728 |
| 5 | 557.7092718 |
| 6 | 1545.220047 |
| 6 | 1354.779953 |
| 7 | 1125.799336 |
| 7 | 1773.200664 |
| 8 | 1602.011446 |
| 8 | 1198.988554 |
| 9 | 297.5253945 |
| 9 | 2502.474606 |
| 10 | 37.11925691 |
| 10 | 2742.880743 |
| 11 | 650.5460141 |
| 11 | 2054.453986 |
| 12 | 1057.462128 |
| 12 | 1642.537872 |
| 13 | 769.252108 |
| 13 | 1920.747892 |
| 14 | 377.7934778 |
| 14 | 2222.206522 |
| 15 | 2340.171915 |
| 15 | 219.8280848 |
| 16 | 456.5210337 |
| 16 | 2044.478966 |
| 17 | 1425.995879 |
| 17 | 1074.004121 |
| 18 | 17.09018432 |
| 18 | 2389.909816 |
| 19 | 728.0278296 |
| 19 | 1677.97217 |
| 20 | 710.2578621 |
| 20 | 289.7421379 |
Thank you. It works!
