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

What result are you expecting.  Explain the question clearly and show the expected result.

  • rauerfc's avatar
    rauerfc
    Frequent Visitor
    5 years ago

    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.

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