cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rikinshah Regular Visitor
Regular Visitor

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

Accepted Solutions
rikinshah Regular Visitor
Regular Visitor

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

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
rajulshah Senior Member
Senior Member

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

Hello @rikinshah,

 

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. 

mussaenda Senior Member
Senior Member

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

Hi @rikinshah 

 

You can use ALLSELECTEDon your rank formula

rikinshah Regular Visitor
Regular Visitor

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

This gives me 1 rank for all records 

rikinshah Regular Visitor
Regular Visitor

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

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

rikinshah Regular Visitor
Regular Visitor

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

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)