Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 RankYearAirportCityCountryTotal Passengers
12018Hartsfield–Jackson Atlanta International AirportAtlantaUnited States107394029
22016Hartsfield–Jackson Atlanta International AirportAtlantaUnited States104171935
32017Hartsfield–Jackson Atlanta International AirportAtlantaUnited States103902992
42015Hartsfield–Jackson Atlanta International AirportAtlantaUnited States101491106
52018Beijing Capital International AirportChaoyang-ShunyiChina100983290
62014Hartsfield–Jackson Atlanta International AirportAtlantaUnited States96178899
72017Beijing Capital International AirportChaoyang-ShunyiChina95786442
82016Beijing Capital International AirportChaoyang-ShunyiChina94393454
92015Beijing Capital International AirportChaoyang-ShunyiChina89938628

 

EXPECTED : 

Airport RankAirportCityCountryTotal Passengers
1Hartsfield–Jackson Atlanta International AirportAtlantaUnited StatesSum of  this airport for all 4 years
2Beijing Capital International AirportChaoyang-ShunyiChinaSum of  this airport for all 4 years

 

Appriciate your help. Thanks 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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. 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Super User
Super User

Hi @Anonymous 

 

You can use ALLSELECTEDon your rank formula

Anonymous
Not applicable

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

rajulshah
Super User
Super User

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
Not applicable

This gives me 1 rank for all records 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.