Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
I found my answer after many tries. Posting here could be useful for some one in future.
I found my answer after many tries. Posting here could be useful for some one in future.
Hi @Anonymous
You can use ALLSELECTEDon your rank formula
I used ALL and it worked for this solutions. I posted the solution as I found it.
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.
This gives me 1 rank for all records
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |