Forum Discussion
Ranking Cumulative Total
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])
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?
can you post some data?
The data I am working is unfortunately confidential and I would be breaching my company's external data sharing policy.
I'm only using one table with a quantity amount column and text-based part number column.
