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

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