I am struggling to get RANKX to work as expected and would gratefully appreciate some help…
Please see attached PBIX file, which has two tables: Location and Sales: https://www.dropbox.com/s/mxg2qb0c4ejcaio/Sample%20Top%20and%20Bottom.pbix?dl=0
Location is having 3 columns(county, City and Location code) there are multiple Cities under same county and multiple location code under same City)
Sales is having 3 columns(LocationCode, Product and Sales)
The problem I am facing here is I need to rank the top and bottom sales by County and City. However since there are multiple locations under same city the rankx function is not giving me the desired output. If I put column LocationCode into the table it works, but as soon as I remove it, it breaks.
This is the DAX I am using to calculate the Top Rank = RANKX(ALLSELECTED(Sales),[Total Sales],,DESC) and for Bottom Rank = RANKX(ALLSELECTED(Sales),[Total Sales],,ASC)
Additionally, on the 2nd tab ‘Matrix’, I would like to know how to get my RANKX measures to work, regardless of the hierarchy displayed, eg whether by County or by City.