RANKX of a summary using three (3) columns / dimensions

[stochasticKL]

Hi, 

I have a source table with four dimensions: Region > Country > Area > Store and a value column Sales. I want to create a measure that will rank each store inside an area depending on its sales. I tried to replicate solutions here in the community were there are only two columns. Something similar to this: 

STORE_RANKING = 
RANKX ( 
  FILTER ( 
    ALL ( 
      Table[Region],
      Table[Country],  
      Table[Area],
      Table[Store]
    ),
    Table[Store] = MAX ( Table[Store] )
  ),
  CALCULATE ( 
    SUM ( Table[Sales] )
  )
)

I think I'm missing the logic behind calling all Store Values only for a specific Region && Country && Area. The output should look similar to this: 

Region	Country	Area	Store	Sales	Rank
R1	A	X	S0	12	1
R1	A	X	S1	1	2
R1	A	Y	S2	20	1
R1	A	Y	S3	9	2
R1	A	Y	S4	5	3
R1	A	Z	S5	30	1
R1	A	Z	S6	7	2
R1	B	X	S7	48	1
R1	B	X	S8	11	2
R1	B	Y	S9	24	1
R1	B	Y	S0	19	2
R1	B	Y	S1	7	3
R1	B	Z	S2	50	1
R1	B	Z	S3	39	2
R1	B	Z	S4	6	3
R2	A	X	S5	33	1
R2	A	X	S6	23	2
R2	A	Y	S7	44	1
R2	A	Y	S8	40	2
R2	A	Y	S9	1	3
R2	A	Z	S0	36	1
R2	A	Z	S1	6	2
R2	B	X	S2	48	1
R2	B	X	S3	21	2
R2	B	Y	S4	26	1
R2	B	Y	S5	16	2
R2	B	Y	S6	5	3
R2	B	Z	S7	9	1
R2	B	Z	S8	7	2
R2	B	Z	S9	3	3

Any help would be greatly appreciated.

[v-jayw-msft · Accepted solution]

hi @stochasticKL ,

please check following steps as below:

1. create Measure = SUM('Table'[Sales])

2. Create Measure 2 = RANKX(ALL('Table'[Store]),[measure])

Result would be shown as below:

BTW, Pbix  as attached.

Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.