Create New Column with RANKX DAX for ranking by multiple variable

[Anonymous]

Hi All, 

I would like to create new column (Rank) using RANKX, this rank coloumn is group by multiple criteria (Type 1, Type 2, Month, and Year) then rank by the score. below are the sample of my table. 

Type 1	Type 2	Month	year	Score	Rank
A	AA	JAN	2023	100	1
A	AB	JAN	2023	10	1
A	AB	FEB	2023	100	1
A	AA	FEB	2023	200	2
A	AA	FEB	2023	300	1
B	BB	MAR	2023	100	1
B	BB	MAR	2023	50	2
B	BA	FEB	2023	60	1
B	BA	FEB	2023	30	2
C	CC	FEB	2023	40	2
C	CC	FEB	2023	100	1
C	CC	JAN	2023	90	1
C	CD	MAR	2023	80	1
C	CE	MAR	2023	70	1
D	DE	JAN	2023	50	2
D	DD	JAN	2023	45	2
D	DD	JAN	2023	67	1
D	DE	JAN	2023	99	1

anyone have using RANKX function for this use case ? appreciate much of your help. 

[ThxAlot · Accepted solution]

Rnk = 
COUNTROWS(
    FILTER(
        DATA,
        DATA[Type 1] = EARLIER( DATA[Type 1] )
            && DATA[Type 2] = EARLIER( DATA[Type 2] )
            && DATA[year] = EARLIER( DATA[year] )
            && DATA[Month] = EARLIER( DATA[Month] )
            && DATA[Score] >= EARLIER( DATA[Score] )
    )
)

[Anonymous]

This is working ! thank you very much!

[mlsx4]

Hi @Anonymous 

This should work:

sumSC= SUM(Table[Score])

RANX Total= 
RANKX(
ALLEXCEPT('Table','Table'[Type 1],'Table'[Type 2],'Table'[Month],'Table'[year]),[sumSc],,DESC
)