Group Sum Rank calculation

[Anonymous]

Hi guys quick one:

I want to determine the product rank against the sum of quantity relevant to that product.

For example, I have ~30 products, and the table contains 100k + rows of data on the quantities for those product (where one line represent a product and the quantity). If the sum of product 4 = 100, I want to return what its rank is amongst the sum of the other product quantities (a group sum rank I suppose you could call it). Each row for product 4 (or x), the rank should be same, as its calculated overall, and then 1-30 for the other products....

Im currently using this and its not working: 

product index = RANKX( ALL('Items'),[quantity],,ASC,Dense)

[amitchandak]

@Anonymous ,For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Anonymous]

// Assuming that all your required data is
// in one table (which by the way is a
// very bad design for too many reasons to
// state here), you can write...

[Total Quantity] = SUM( Items[Quantity] )

[Product Rank] =
IF( HASONEFILTER( Items[Product] ),
	
	// Get all the products'
	// quantities first
	RANKX(
		// These are all the products
		// that you want to use
		// in order to find the rank
		// of your selected product.
		// If you want to find the rank
		// of a product using only the
		// visible products in your visual,
		// you have to change ALL(...) to
		// ALLSELECTED(...).
		ALL( Items[Product] ),
		// This calculates the total qty
		// for each of the products.
		[Total Quantity],
		/* Value for the current product
		** is [Total Quantity] */,
		ASC,
		DENSE
	)
)

Best

D

[harshnathani]

Hi @Anonymous ,

Try this measure

Measure = 
RANKX (
        ALL ( Table1 ),  
    ),
    CALCULATE ( SUM ( Table1[Qty] ) ),
    
)

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!