Return rank by group (identifying group selected member belongs to)

[abayless]

I have two tables:

Sales data -- contains [Revenue], [ID]
Store List -- contains [Market], [Store], [ID]
These tables are linked by [ID]. I have a page-level filter on 'Store List'[Store], as its a report that allows you to see various details on an individual store. What I need is a way to return the individual stores [Revenue] rank within its own market. So if you select store 123 and its in Market 1, then I'd like to return the individuals stores [Revenue] rank among only stores in Market 1. Rank should look something like this:

Market     Store      Revenue      Rank

Mar 1        123           100            2

Mar 1        345           110            1

Mar 1        267            95             3

Mar 2        357           548            1

Mar 2        795           405            2

Since I have a filter for store, I'd like to create a card that just returns this Rank when I select a store. Most of my issue seems to be not understanding how to make that selection hierachy work and correctly group. If I select a single store, I need to identify the market it belongs to, rank all the stores in that market, then return that single stores rank.

Here are some things I tried:

Rank = RANKX ( ALLSELECTED('Store List'),CALCULATE(SUM('Sales Data'[Revenue])))
This gives every selected store a rank, but not automatically according to the [Market] group. If I filter down to a market it will rank just the selected stores, but it defaults to 1 whenever a single store is selected.

Rank = RANKX ( ALLSELECTED('Store List'[Market]),CALCULATE(SUM('Sales Data'[Revenue])))
This was suggested online to account for the grouping, but it doesn't seem to work at all. All stores are given a rank of 1.

Any help would be greatly appreciated.

[Chihiro · Accepted solution]

Try something like...

Rank (Market) =
RANKX (
    FILTER (
        ALL ( 'Store List'[Store], 'Store List'[Market] ),
        'Store List'[Market] = MAX ( 'Store List'[Market] )
    ),
    CALCULATE ( SUM ( 'Sales Data'[Revenue] ) )
)

[abayless]

Thank you -- this worked! Additional question: what is the purpose of the MAX function here? 

[Chihiro]

MAX is used to filter table columns by row context of [Store] within Filter function to reduce table to smaller set.

You can find more detail explanation of the concept used in link below.

http://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures