Create new table by performing Group, SUM and RankX on another table.

[Anonymous]

I have a list of Airports with total passenger number for say 4 years as shown in below table. 

Right now When I do RankX it shows correct rank for each row based on Total passengers. 

I want to group same [Airport] names in one row, calculate [TotalPassengers] for all years for that Airport and then Rank the Airport.

Current Data : 

Airport Rank	Year	Airport	City	Country	Total Passengers
1	2018	Hartsfield–Jackson Atlanta International Airport	Atlanta	United States	107394029
2	2016	Hartsfield–Jackson Atlanta International Airport	Atlanta	United States	104171935
3	2017	Hartsfield–Jackson Atlanta International Airport	Atlanta	United States	103902992
4	2015	Hartsfield–Jackson Atlanta International Airport	Atlanta	United States	101491106
5	2018	Beijing Capital International Airport	Chaoyang-Shunyi	China	100983290
6	2014	Hartsfield–Jackson Atlanta International Airport	Atlanta	United States	96178899
7	2017	Beijing Capital International Airport	Chaoyang-Shunyi	China	95786442
8	2016	Beijing Capital International Airport	Chaoyang-Shunyi	China	94393454
9	2015	Beijing Capital International Airport	Chaoyang-Shunyi	China	89938628

EXPECTED : 

Airport Rank	Airport	City	Country	Total Passengers
1	Hartsfield–Jackson Atlanta International Airport	Atlanta	United States	Sum of  this airport for all 4 years
2	Beijing Capital International Airport	Chaoyang-Shunyi	China	Sum of  this airport for all 4 years

Appriciate your help. Thanks 

[Anonymous · Accepted solution]

I found my answer after many tries. Posting here could be useful for some one in future. 

RankByPassnger = SUMMARIZE(ALL(StatsByAirPassengers[Airport], StatsByAirPassengers[AirportCode], StatsByAirPassengers[City]),StatsByAirPassengers[AirportCode],StatsByAirPassengers[Airport],StatsByAirPassengers[City],"TotalPassengers",[Total Passengers])

Thanks for help who replied. 

[mussaenda]

Hi @Anonymous 

You can use ALLSELECTEDon your rank formula

[Anonymous]

I used ALL and it worked for this solutions. I posted the solution as I found it. 

[rajulshah]

Hello @Anonymous,

Please try the following DAX Query for the Airport Rank Measure:

Airport Ranks = 
RANKX(ALLSELECTED(Airports[Airport],Airports[City],Airports[Country]),CALCULATE(SUM(Airports[Total Passengers])),,DESC,Dense)

Hope this helps. Please let me know if this didn't help. 

[Anonymous]

This gives me 1 rank for all records