Forum Discussion

rauerfc's avatar
rauerfc
Frequent Visitor
5 years ago
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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

You are welcome.  So what problem are you facing?  Please be clear.

rauerfc's avatar
rauerfc
Frequent Visitor
5 years ago

So, each account manager has a different best selling, second best, third best MRK. 

My visualization was built to show 4 graphs that will return the same information (Coverage), for the first, second, third, and fourth best-selling MRK. 

How can I show only information from the best selling in the first graph, 2, 3 and so?

 I'm trying to filter with the rankx selecting 1 and isn't working:

rank_mrk = RANKX(ALLSELECTED(tab_mrk[MRK]),[sum_rank],, DESC, Dense)

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Share the link from where i can download your PBI file.