Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

RANKX with filter

Hi! I'm trying to create a DAX measure using RANKX, but I need help. I start with the table to the left where I see all my products and what rank they have based on sales. Then I want to be able to ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    I doubt that [Sales] here is a field without summarize.

    If so, please sum this field.

    then please new:

    Total Sales = SUM('Table'[Sales])
    Rank = RANKX(ALL('Table'[Product],'Table'[Supplier]),[Total Sales])

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

Anonymous , Issue is not very clear. Please try with allselected

 

RANKX(
ALLSELECTED('Table'[Product], 'Table'[Supplier]),
[Sales]
)

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi,

    Sorry about that! Will try to make the issue clearer. I want to create a measure that, when filtering a table, shows the ranking of the filtered products in a context as if the filter is not applied, i.e. when I filter on

    "Supplier1", I want to see that product A has rank 1 and product F has rank 6, which is the total ranking of all products without any filter on supplier.

    When using your measure, I get the following result:

    When filtering on "Supplier1" I want to show Product A as rank 1, and product F as rank 6 - not rank 2. Unfortunately my organization doesn't allow me to share files over the internet...

    Thanks!!

    Best regards,

    Ella

     

    • Rachid1679's avatar
      Rachid1679
      Regular Visitor
      2 years ago

      Hello Ellac 

      Hope you're doing well.

       

      I'm facing the same issue with rankx measure. Were you able to find a solution ?

       

      Thank you for the support

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Rachid1679,

         

        Yes! The accepted solution of this thread worked 🙂 The resulting measure was this:

         

        VAR __variableRank = RANKX(ALL('Table'[Product], 'Table'[Supplier]), [Sales])
        RETURN
                IF(
                    __variableRank < 101,
                    __variableRank,
                    BLANK()
                )
         
        I ended the measure expression with an if statement which gives me top 100 on the ranking.
         
        Hope it works for you too!
        /Ella