cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tanlamquach Frequent Visitor
Frequent Visitor

Ranking Not Working Properly

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
Capture.PNG

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Ranking Not Working Properly

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] )
    )
)

Ranking.jpg

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Ranking Not Working Properly

Hi @tanlamquach 

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

   

Super User
Super User

Re: Ranking Not Working Properly

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] )
    )
)

Ranking.jpg

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 271 members 2,770 guests
Please welcome our newest community members: