Ascending order in Rankx not filtering

[JJohns]

Apologies if this post seems familar, I accidently accepted a solution in my previous post but I am am still having the same issue. I have included a sample dataset that mimics the issue I am having. I have a data set that I am ranking each hierarchy in ascending order. The first level works correctly and ranks properly, the issue is that once I drill down to the next level rankx correctly ranks the subset but wants to start at the bottom of the entire population vs ranking the subset. I am attaching a sample test set that replicates my exact issue. The table has 3 columns State, Office, Amount. I created a measure: Amount_2 = CALCULATE(SUM(Amount)) so that I have a measure to rank. I then use the following Dax for my Rankx measure: 

Rank =

IF(

     ISFILTERED ( Data[Office] ),

     RANKX ( ALLSELECTED(Data[Office]), Data[Amount_2],,ASC) ,

    IF (

        ISFILTERED ( Data[State] ),

        RANKX ( ALLSELECTED(Data[State]), Data[Amount_2],,ASC)

    )

)  

After creating the measure I create a matrix with state and office as row variables, amount and rank in the values. I get the following results:

Results when expanded to office:

Results I am expecting:

Table used:

State	Office	Amount
SC	100	39
SC	101	29
SC	102	42
SC	103	45
SC	104	76
SC	105	95
SC	106	92
SC	107	87
IL	200	77
IL	201	3
IL	202	50
IL	203	67
IL	204	25
IL	205	71
IL	206	58
IL	207	72
IL	208	88
IL	209	78
IN	300	37
IN	301	80
IN	302	39
IN	303	4
IN	304	82
IN	305	60
IN	306	59

Any help is greatly appreciated. Thanks

[Anonymous · Accepted solution]

@JJohns As per your data,

You can use the below dax to get the ranks based on states and Office's

Rank_Office Level = 
IF(ISINSCOPE(Ranking[Office]),
    RANKX(
        CALCULATETABLE(VALUES(Ranking[Office]),
        ALLSELECTED(Ranking[Office])
        ),
        [Total Amount]
    ),
    IF(ISINSCOPE(Ranking[State]),
    RANKX(
        ALLSELECTED(Ranking[State]),
        [Total Amount]
    )
    ))

Accept the solution if it helps you.

Thanks,

Mohan V

[JJohns]

Thanks Mohan, that worked perfectly, I appreciate the help!