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

Hi,

You may download my PBI file from here.

Hope this helps.

rauerfc's avatar
rauerfc
Frequent Visitor
5 years ago

Thank you for your reply. 

I used your solution and it worked, is giving me the best selling, second best etc. 

And now my goal is to use this as a filter for the graph, for example:

So I want to filter information from the second-best for this graph:

I'm using your formula: 

mrk_cons_2 = CALCULATE(FIRSTNONBLANK(TOPN(1, VALUES(tab_mrk[MRK]),[sum_rank]),1), FILTER(VALUES(tab_mrk[MRK]),[rank_mrk]>1))

 

  • 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.