Rank dynamically

[CarlsBerg999]

Hi,

I have data with multiple groups. I want to rank them within each group. For example:

Product category	Product	Sales volume	Rank
Machinery	A	50	3
Machinery	B	320	1
Machinery	C	51	2
Equipment	D	70	2
Equipment	E	80	1
Other	F	600	1

How can i create a measure that does the ranking dynamically? I don't want to write a filter where i need to type a the names of all the categories separately. 

Thank you in advance!

[Fowmy · Accepted solution]

@CarlsBerg999 

Can you try the following measure?

RNK = 

RANKX( 
    ALLEXCEPT(Table1,Table1[Product category]),
    CALCULATE(SUM(Table1[Sales volume]))
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[AllisonKennedy]

@CarlsBerg999 You can try something like in this blog post. For your sample data, replace [Category] with [Product Category] and [Sub Category] with [Product]: 

RANKX (

FILTER(

ALL(

'Table'[Category],

'Table'[Sub Category]

),

'Table'[Category] = MAX('Table'[Category])

),

CALCULATE(SUM('Table'[My Value]))

)

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

[CarlsBerg999]

This returns a ranking of "1" to all rows for some reason.