Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rankx producing ranking ties when data shows they are not

I have a table that I am trying to rank sales by part number year-to- date.  The data includes columns for date sold, #sold, part number and current stock level which can be blank, 1 and on up.

Measures:

YTD Items Sold_m = TOTALYTD(SUM('dataDimStock'[Dim Stock.Sold]),'Date Dimension'[Date])

ItemsSoldRank_m = RANKX(ALL(dataDimStock),[YTD Items Sold_m],,DESC,Dense)

I have tried several variations of the rankx measure and all show ties where there should be none.

I am also trying to get a top 5 rank with the following measure:

ItemsSoldTop5Rank = CALCULATE([YTD Items Sold_m],FILTER(dataDimStock,[ItemsSoldRank_m]<=5))

 


Ranking Ties.jpg

As you can see in the snapshot, I am getting ties for values in YTD Items Sold_m that are clearly different and my top 5 measure is showing two parts with 64 and 37 as a value and a sum of 101 at the bottom of the visual.  Any assistance would be greatly appreciated.

Thanks,

Jeff

 

 

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Try this measure

ItemsSoldRank_m = RANKX(ALL('dataDimStock'[Dim Stock.Part Number]),[YTD Items Sold_m],,DESC,Dense)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Ashish,

That adjustment works.  Thank you.  How would I go pulling the rank in a top 5, 10, 100, etc. and sum the number of those parts sold in those top categories?  My goal is to compare the number sold against on an in-stock column  based on the top n rank to see what % of our top selling parts that are in stock.

Sincerely,

Jeff

Anonymous
Not applicable

Here is an example of a top 5 measure that I have tried:  

ItemsSoldTop5Rank = CALCULATE(dataDimStock[YTD Items Sold_m],FILTER(dataDimStock,[ItemSoldRank_m3]<=5))
The ItemSoldRank_m3 measure is the measure that works to generate the ranking that Ashish provided.  In the attached snapshop, based on a visual top 10 filter, I get the fill rate.  I need a measure to calculate the top 5, 10, etc. so that I don't have to see all the individual part numbers to get the calculations.
Top10.jpg
Any assistance would be greatly appreciated.
Thanks,
Jeff

Hi,

Share the link from where i can download your workbook and please show the exact result that you want there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank you Ashish.  

 

Here is an example of the data resultes layout I am trying to create.

 

ExampleDesiredLook.jpg

 

Here is a link to the file in Dropbox.

 

https://www.dropbox.com/l/scl/AAAikGgjuAtmy01ZRI_4WhnPdnNlwlfqWtY

 

Sincerely, 

 

Jeff

Cmcmahan
Resident Rockstar
Resident Rockstar

You've got an issue in your rank measure.  You never actually set up an expression to be tested against the table:

 

ItemsSoldRank_m = RANKX(ALL(dataDimStock),[YTD Items Sold_m],[YTD Items Sold_m],DESC,Dense)
Anonymous
Not applicable

I just updated the measure to: 

ItemsSoldRank_m = RANKX(ALL(dataDimStock),[YTD Items Sold_m],[YTD Items Sold_m],DESC,Dense)
the results did not change.
Thanks,
Jeff

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.