Rank sales of each province

[nguyendaithang]

Hi every one

I have a problem with rank as below. I have a below table with 5 provinces, each province apprears differently (1,2 or rows)

So I must ranks sales based on province as belows. Example: Province A has 3 rows of sales: 70,51,78 => Equivalent Rank: 2,3,1

Therefore I need a DAX formula for rank this table

Thank you very much!

PROVINCE	SALES	RANK
A	70	2
B	23	1
A	51	3
C	34	2
D	29	2
D	14	1
B	26	2
E	22	1
A	78	1
C	22	1

[joshcomputer1 · Accepted solution]

Try this as a calculated column:

Rank = rankx(filter(table1, Table1[province]= earlier(Table1[province])),table1[sales])

[nguyendaithang]

waw, that works perfectly

Thank you very much!

[Baskar]

@nguyendaithang

Try this.

Rank = RANKX(Filter('Rank', 'Rank'[PROVINCE] = EARLIER('Rank'[PROVINCE])),'Rank'[SALES],,DESC,Dense)