Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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))
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
Hi,
Try this measure
ItemsSoldRank_m = RANKX(ALL('dataDimStock'[Dim Stock.Part Number]),[YTD Items Sold_m],,DESC,Dense)
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
Here is an example of a top 5 measure that I have tried:
Hi,
Share the link from where i can download your workbook and please show the exact result that you want there.
Thank you Ashish.
Here is an example of the data resultes layout I am trying to create.
Here is a link to the file in Dropbox.
https://www.dropbox.com/l/scl/AAAikGgjuAtmy01ZRI_4WhnPdnNlwlfqWtY
Sincerely,
Jeff
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)
I just updated the measure to:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |