Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |