Forum Discussion
Ranking Cumulative Total
Greetings PBI Community,
I'm having issues with figuring out how to write a DAX formula that would calculate the rank of different part_numbers based off of their cumulative total.
This is my current formula but it's all wrong. Any help would be greatly appreciated.
= RANKX(Distributor_Item_Orders, Distributor_Item_Orders[Shipped_quantity], , ASC)
This is an example of what I'd like to see, just a simple breakdown of ranking by quantity size, there may be multiple rows with the same part number as well.
Thanks,
Nic N
I see what I did, the ALL needs to be ALL ( Table[column] ) not just ALL ( Table). So using the Contoso DB:
We have colors on rows, so that is the inital filter context. Have a measure to count the # of products from the products table. So with colors on rows, we will get the amount of products filtered by the color.
Base Measure:
Total Products = COUNTROWS('Product')Rank Measure:
RANK = RANKX( ALL( 'Product'[Color]), [Total Products])Using ALLSELECTED will give a "local" rank vs. a "global" rank of using ALL:
RANK Using AllSelect = RANKX( ALLSELECTED( 'Product'[Color]), [Total Products])
7 Replies
Couple thinngs to keep in mind with RANKX:
1) need to use ALL ( Table). Otherwise when it goes to rank, it will only "See" the current row. So every rank will be 1
2) The Expression used either needs to be a measure (which would be in [measure]) or wrapped in CALCULATE. Both accomplish the same thing, which is context transition (which turns the current row context into an equivalent filter context)
3) The HASONEVALUE removes a rank from the grand/subtotals. But that is optional
RankX Example= IF( HASONEVALUE( Distributor_Item_Orders[Part_Number), RANKX( ALL ( Distributor_Item_Orders), CALCULATE( SUM(Distributor_Item_Orders[Shipped_quantity])), ASC ) )
Nick,I appreciate the feedback and support, the formula is working slightly when I use ALLSELECTED(), However the ranking is still not correct when comparing the total quantity of a part number.
Any suggestions?
