Forum Discussion
RANKX returning variable, TOP 1, 2, 3, 4
I have a table that has sales by month, account manager, and MRK.
I need that the measure returns the TOP 1 selling MRK and another one for 2, 3, and 4.
Output = 130 or 40 or 110
Each measure will be used in 4x charts, each one for the TOP 1,2,3,4, and this could vary by Account Manager.
Each Acc Manager could have differents TOP 1, 2, 3, 4 consequently, different charts.
Sample of dataset:
| MRK | Manager | Month | Quantity Sales |
| 10 | 921 | Mar-21 | 9 |
| 10 | 952 | Mar-21 | 2 |
| 40 | 921 | Mar-21 | 22 |
| 40 | 952 | Mar-21 | 53 |
| 40 | 4147 | Mar-21 | 38 |
| 40 | 24829 | Mar-21 | 6 |
| 110 | 4147 | Mar-21 | 0 |
| 130 | 951 | Mar-21 | 210 |
| 150 | 943 | Mar-21 | 58 |
| 150 | 947 | Mar-21 | 307 |
| 150 | 951 | Mar-21 | 324 |
| 150 | 952 | Mar-21 | 26 |
| 170 | 41687 | Mar-21 | 129 |
| 170 | 921 | Mar-21 | 2 |
| 170 | 922 | Mar-21 | 12 |
| 170 | 923 | Mar-21 | 201 |
| 170 | 924 | Mar-21 | 85 |
| 170 | 943 | Mar-21 | 8 |
| 170 | 947 | Mar-21 | 28 |
| 170 | 951 | Mar-21 | 221 |
| 170 | 952 | Mar-21 | 26 |
| 170 | 3627 | Mar-21 | 50 |
| 170 | 4147 | Mar-21 | 52 |
Hi rauerfc ,
Please try the following formula to create measures :
sum = CALCULATE(SUM('Table'[Quantity Sales]),ALLEXCEPT('Table','Table'[MRK]))rank = RANKX(FILTER(ALL('Table'),'Table'[Manager]=MAX('Table'[Manager])),[sum],,DESC,Dense)If you want to separately get the rank value , you could use this:
Top 1 MRK = CALCULATE(MAX('Table'[MRK]),FILTER('Table',[rank]=1))The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
What result are you expecting. Explain the question clearly and show the expected result.
I need one measure that's going to calculate the TOP 1 selling MRK.
So if I select this Acc. Manager, the best-selling MRK will be 130.
And another measure to return the second best, third best, and fourth-best.
Hi rauerfc ,
Please try the following formula to create measures :
sum = CALCULATE(SUM('Table'[Quantity Sales]),ALLEXCEPT('Table','Table'[MRK]))rank = RANKX(FILTER(ALL('Table'),'Table'[Manager]=MAX('Table'[Manager])),[sum],,DESC,Dense)If you want to separately get the rank value , you could use this:
Top 1 MRK = CALCULATE(MAX('Table'[MRK]),FILTER('Table',[rank]=1))The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
