Serial Number based on the Alphabetical order

[Balaraju]

Hi Everyone,

I have a scenario, I have multiple countries each country having multiple States. I need to create serial number column for states in each country. attaching sample data below.

Country	States
India	AP
India	Karnataka
India	Tamilanadu
India	UP
USA	Arizona
USA	Colorado
USA	Florida
USA	Montana
USA	Maine

Attaching expected out put.

Country	States	Serial No
India	AP	1
India	Karnataka	2
India	Tamilanadu	3
India	UP	4
USA	Arizona	1
USA	Colorado	2
USA	Florida	3
USA	Montana	5
USA	Maine	4

Please help me out on this.
Thanks,
Bala.

[ryan_mayu · Accepted solution]

@Balaraju 

you can try this to create a column

Serial No = rankx(FILTER('Table','Table'[Country]=EARLIER('Table'[Country])),'Table'[States],,ASC)