Ranking Not Working Properly

[Anonymous]

Hi guys

Need help before I pull all my hair out

The ranking works fine for everything except for the red circled, it should be 1 instead of 2


Sales Rank =
IF (
ISINSCOPE ( 'Product'[Product Category] ),
RANKX ( ALL ( 'Product'[Product Category] ), [Sales Amount],, DESC ),
IF (
ISINSCOPE ( 'Product'[Product Line] ),
RANKX ( ALL ( 'Product'[Product Line] ), [Sales Amount],, DESC ),
BLANK ()
)
)

Here is the link of the pbix file on google drive

https://drive.google.com/open?id=1lBfXbT8ZFWSEcbKizBypfrGrJez4Ptp7

Thanks

[AlB]

Hi @Anonymous 

That looks strange, yes. I don't see anything wrong with your code. In fact, to make it stranger, I was running some tests and realized by chance that by adding another measure to the matrix visual, your measure all of a sudden yields the correct result. If I take my mock measure [Sales rank V3] off the visual, your measure yields the wrong results again. Try it on "Page 2" that I've added to your original file (see attachment). Add and remove [Sales Rank V3] to the visual and see how the result for "Circuit protection" changes between 1 and 2

Maybe knowledgeable people like @Zubair_Muhammad, @MFelix,  @Greg_Deckler can have a look and help here

[jdbuchanan71 · Accepted solution]

That is very odd behavior, I can only think that it has something to do with [Product Category] "Fuses" have a sales amount larger than the #2 ranked [Product Line].  

However, switching it to to using HASONEVALUE seems to fix it.

Ranking = 
IF ( HASONEVALUE ( 'Product'[Product Category] ),
    RANKX ( ALL ( 'Product'[Product Category] ), [Sales Amount] ),
    IF ( HASONEVALUE ( 'Product'[Product Line] ),
        RANKX ( All ( 'Product'[Product Line] ), [Sales Amount] )
    )
)