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 am having issues creating a RANKX function that ranks values that are in the same period. My data contains multiple cities with multiple years and quarters for each city (Ex: Dallas for 1Q 2021, 2Q 2021..../Phoenix for 1Q 2021, 2Q 2021...). For each period I would like to rank the cities with regards to a specific metric (i.e vacancy). I have tried to accomplish this both through adding a column and meaures but have been unable to make it work.
My current measure formula is:
@jjones90274 , Try measure like
Rankx(allselected(Table[City]) , CALCULATE(SUM('Costar Data'[Vacancy Rate])), , desc,dense)
Rankx(filter(allselected(Table[City], Table[Qtr Year]), [Qtr Year] =max(Table[Qtr Year]) , CALCULATE(SUM('Costar Data'[Vacancy Rate])), , desc,dense)
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
I also tried another method that got me close but it is still off. It does not seem to be ranking properly and the ranking does not start at 1 (see below):
The formula for GSVacancy Rank is:
Thanks for the reply @amitchandak! I gave the first measure a try and I am still getting duplicate rankings (see below)
For reference I have included a snapshot of the relationships I have in the model.
The data I am using has both past dates with historical data and future dates with forcasted data. I tie the two data sources together through the calendar and market key tables. In my report I have slicers for Date (Year and Qtr) and Market.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |