RANKX(ALL SELECTED… DAX causing duplicates

[Kirk_1]

Here is my DAX expression:
$ RANK = RANKX(ALLSELECTED('Store-Level'), CALCULATE(SUM('Store-Level'[$])), ,DESC)

This DAX works in my smaller filtered table (right), but I have another larger filtered table (left) with 175,000 rows and I am getting duplicate ranking numbers. The overall data has 500,000 rows.

Is there a limit to how many rows a RANKX ALLSELECTED DAX formula will handle? Is there a better way to write the DAX?

Link to sample PBI file 7.6MB edited to exclude identifiable product info. Thanks!

https://we.tl/t-2cqgLnGP4R 

[ThxAlot]

ALLSELECTED(Table) and ALLSELECTED(Table[Column]) are totally different. There's no point ranking every row of the table by ALLSELECTD(Table).

[Kirk_1]

The actual dataset is about 875,000 rows, but there is a filter on the entire tap that filters out customers down to a specific customer. This filters it down to about 500,000.  I am trying to get the ranking column on the 500K rows, but also want it to re-rank based on various filters down to brand, category, etc.

[Kirk_1]

Manoj Nair,

It did take a lot longer to process using that, but unfortunately it did not work. It still shows duplicated ranks, like the majority of the top $ sales items have a "1" as the the rank number.

[Manoj_Nair]

@Kirk_1- Try this

$ RANK = 
RANKX(
    ALLSELECTED('Store-Level'), 
    CALCULATE(SUM('Store-Level'[$])) + COUNT('Store-Level'[StoreId]) / 1000000,
    ,
    DESC
)

In this revised measure, the bridge is created by adding a very small value to the sum. The value is so small (the count divided by 1,000,000) that it doesn't affect the overall sum, but it is enough to make each sum unique, which prevents the RANKX function from assigning the same rank to different rows.

If this post helps to find solution would be happy if you could mark my post as a solution and give it a thumbs up

Best Regards

Manoj Nair
Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/