Ranking Cumulative Total

[nnouchi]

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

[Anonymous]

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

[nnouchi]

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?

[Anonymous]

can you post some data?  

[nnouchi]

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.

[Anonymous · Accepted solution]

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

[nnouchi]

Thanks Nick, I found out the reason behind why it was originally showing in order, I had negative quantities in my table that were being calculated in the ranking and that I needed to filter within the report. I appreciate the help.

[Anonymous]

no problem. ill just use some dummy data from the sample DBs that msft puts out.