Forum Discussion

Asad_S's avatar
Asad_S
New Member
3 years ago

Help with RankX

I have two connected tables, one contains Item information (Numbers, description, price, cost etc) and the other is the item ledger which tracks quantities sold by Item numbers. They are connected using Item Number (Item Infomation Table) as a 1  to Many (Item ledger) relationship. 

 

I am able to put together tables that give me sales by Item number, one way is to use a calculated measure (quantity x price), and the other is by bringing in the price from the Item table into the Item ledger via look up and in the table multiply the price times the quantity. 

 

I am now trying to use RankX to Rank the Item Numbers by Total Sales and I can't get the function to work correctly. 

 

RankX formulas : 

 
Rank = RANKX(ALL('Item Table'[Item Number]),[Total Sales],,DESC) using the Calculated Measure Mentioned Above the first This Rank formula gives me a rank of 1, 2 and 3. (see snippet below)
 
Rank 2 = RANKX(ALL('Item Table'[Item Number]), SUM('Item Ledger Combined'[Gross Sales]),,DESC) using the Calculated Column in the item table mentioned above.  and the rank 2 formula only gives me a value of 1 for all rows. See Snippet Below.
 
 
Clearly, both of these are not calculating rank correctly. Any insight into how this can be done correctly would be appreciated as i'll need to replicate the rankx by quantity and cost as well. 
 

 

 

 

DAX
RANKX