Forum Discussion
Solved
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...
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.
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.