Calculated column with rank based on value, plus dependency on 2 categories

[Evelien]

Hi,

I am looking for the DAX formula to create a calculated column to show the rank by value, per month and per product.

The rank column should look like this:

Month	Product	Company	Value	Rank
1/01/2017	A	Hello	300	1
1/01/2017	A	Bla	250	2
1/01/2017	A	Hey	100	3
1/02/2017	B	Hello	20	3
1/02/2017	B	Bla	40	2
1/02/2017	B	Hey	50	1
1/01/2017	B	Bla	245	1

Many thanks for your help!

Kind regards,

Evelien

[Vvelarde · Accepted solution]

@Evelien

Hi, try with this:

Rank-CC =
RANKX (
    ALL ( Table1[Company] ),
    CALCULATE (
        SUM ( Table1[Value] ),
        ALLEXCEPT ( Table1, Table1[Month], Table1[Product], Table1[Company] )
    )
)

Regards

Victor

Lima - Peru

[Zubair_Muhammad]

Hi @Evelien

Try this calculated column

RANK_ =
RANKX (
    FILTER (
        ALL ( TableName ),
        MONTH ( TableName[Month] ) = MONTH ( EARLIER ( TableName[Month] ) )
            && TableName[Product] = EARLIER ( TableName[Product] )
    ),
    TableName[Value],
    ,
    DESC,
    DENSE
)

[Evelien]

Hi @Zubair_Muhammad,

Thanks for your help, but I do not get the correct rank when using the formula you suggested :-S