RANKX with filter

[ellac]

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 filter on a supplier, for example Supplier = "Supplier1", and then see the product's ranks based on all the products with no Supplier filter applied.

For the left table I get the right output by this measure:

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

But I don't get the right output for the right table using the measure...

Any ideas on how to solve this?

Thanks in advance!

/Ella

[Anonymous · Accepted solution]

Hi @ellac ,

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]

@ellac , 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/3...

[ellac]

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]

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

[ellac]

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