DAX Measure needed for ranking field across multiple dimensions

[powerbignc]

I have a tabular data model built where in my star schema model there is fact level data and radiating out are my dimension tables that all provide differing levels of metadata associated with the fact tables. I have a power BI report and cube built off of store transactions. My goal is to rank the stores at varying levels of detail. For example, the store level is the most granular level of detail where transactions take place think Walmart or any other store you walk into. I also have a dimension table that maps each store number to a specific district, region, city, state, and so forth. What I would like to do is build a measure that ranks stores across the different metadata. For example, if store number “ABC123” is mapped to the district “John”, and region equals “South”, city equals “Houston”, and state equals “Texas” I would like to know how store number ABC123 ranked against its peers in the district John as well as all of the other grouping fields like City, State, etc….

As an example, store ABC123 might be number one in sales for the city of Houston, however at a state level it might rank number 10, and for all of the South region it might rank overall as #5. So, this measure would need to be able to rank at a store level but show me how it fits across the different meta layers. Below is a quick view of a table output that I would like to build in power BI using this new rank measure. As you can see the first column shows the list of stores and the columns to the right show the respective ranking of that store within each of the bucketed categories.

Keep in mind that each store rolls up to only one district, one region, one state, and one city. You can't have one store that is mapped to two different cities, store level is always a one-to-one relationship between the store and its descriptive fields. Within a particular state, city, region, district you can have many different stores and the goal here is to show for a particular store how it ranks in its respective district against its peers who are also in that same district, or region, or state or city. As you can see in the example below store ABC123 ranks first in its respective region and city and you can see that store JET298 ranks first in its city so we know that ABC123 and JeT298 obviously are not in the same city since they are both first in their respective cities.

In the chart below I show each store and how they rank across their respective metadata fields.

In the chart below I have selected one metadata grouping called CITY and have filtered that city to show only the Houston stores and you can see these are my top nine stores just within the city of Houston. So, this measure needs to be flexible to do both table visuals shown here.

How would I create something like this? Ranking is off Revenue_Amt

Here is a simple view of my model....

[amitchandak]

@powerbignc , this kind of rank can work

first one can work across dimensions, but if other dimensions values repeat, it might not work properly

Rank 2 = rankx(filter( SUMMARIZE(ALLSELECTED(sales), 'Geography'[City], 'Geography'[State]), 'Geography'[State]= Max('Geography'[State])), [Net])

with same dimension

rankx(filter( ALLSELECTED( 'Geography'[City], 'Geography'[State]), 'Geography'[State]= Max('Geography'[State])), [Net])

Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA