Ranking in Ascending order not filtering properly

[JJohns]

Hello, 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 have to hardcode everything as well within measures as I am combining several metrics into one matrix that cannot all be filtered the same. I have tried several variations of the following code:

IF (

ISFILTERED ( 'Roster'[Office #2] ),

RANKX ( ALL ( 'Roster'[Office #2] ), _Ranking_Measures[Data_%]),

IF (

ISFILTERED ( 'Roster'[State] ),

RANKX ( ALL ( 'Roster'[State] ), _Ranking_Measures[Data_%] )

)

)

Attached are results at State Level

Results when drill down to IL:

I am just trying to figure out how to return 1-6 when drilled down. RANKX is properly ranking within the subset, just not using the filtered down count of offices for the ranking numbers. Any help is appreciated. Thanks

[v-henryk-mstf]

Hi @JJohns ,

According to your description, if you want to dynamically rank the results based on filtering, you can use the allselect function.

Keep the results of filtering in the external filtering context. The reference is as follows.

Rank_ =
IF (
    HASONEVALUE ( DimProduct[Color] ),
    RANKX ( ALLSELECTED ( DimProduct[Color] ), [Amount_] ),
    BLANK ()
)

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JJohns]

Hey Henry I accidently accepted this as a solution but I am still having the same issue. 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 Rank 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

I tried altering the code to mimic yours and it still doesn't rank correctly once the row level is expanded. Any help is greatly appreciated. Thanks

[lbendlin]

" I have to hardcode everything as well within measures"  not sure what you mean by that. seems mutually exclusive.

Note that if you use RANKX in measures you need to do a couple extra steps, and you need to stay aware of the filter context for each.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523