RANKX values while excluding some items, with a twist.

[Stemar_Aubert]

Hello,

I have the following measure:

EyzRank =
SWITCH (
    ISBLANK ( 'Actual'[YTD 19] ),
    FALSE (), RANKX ( ALLSELECTED ( Reps[INDUSTRY REP] ), 'Actual'[YTD 19] ),
    BLANK ()
)

 It will rank the sales people based on actuals YTD. The ALLSELECTED is here because when regional managers have the report, they must be able change the ranking selection between world or their region. The SWITCH() is present because I will have multiple ranking methods based on other metrics, but the issue I have is with ALLSELECTED.

When deployed in a table, it works. Trouble is, I need to cut the top 3 individual from this list. And of course, because of ALLSELECTED, I have the following result when I apply a filter:

As you can see, I now only have 19 sales people, but RANKX doesn't behave like I need (though it behave as expected).

Ideal output here would be as follow :

4	T	47.2%
5	F	46.4%
6	Z	44.8%
7	J	44.4%
...	...	...

Can you provide insights on this ? I need to be able able to filter by Region (and other slicers), but must be able to exclude the top 3 items. Thank you.

[v-jayw-msft · Accepted solution]

Hi @Stemar_Aubert ,

You can try add conditions before using RANKX().

Please refer to the formula below.

Measure 2 =
IF (
    ISFILTERED ( 'Table'[region] ),
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[region] = SELECTEDVALUE ( 'Table'[region] ) ),
        [Measure],
        ,
        ASC
    ),
    RANKX ( ALL ( 'Table' ), [Measure],, ASC )
)

Result would be shown as below.

Best Regards,

Jay

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.

[Stemar_Aubert]

@v-jayw-msft 

Thank you for the help. At first it didn't work because I have data in multiple tables. However, using the logic you've provided, I got it working.

EyzRank =
SWITCH (
    ISCROSSFILTERED ( Location[REGION] ),
    FALSE (), IF (
        ISBLANK ( ( 'Actual'[actuals YTD 19] ) ),
        BLANK (),
        CALCULATE (
            RANKX ( ALL ( Reps[INDUSTRY REP] ), 'Actual'[actuals YTD 19] ),
            FILTER (
                ALL ( Location[REGION] ),
                Location[REGION] = SELECTEDVALUE ( Location[REGION] )
            )
        )
    ),
    IF (
        ISBLANK ( ( 'Actual'[actuals YTD 19] ) ),
        BLANK (),
        CALCULATE ( RANKX ( ALL ( Reps[INDUSTRY REP] ), 'Actual'[actuals YTD 19] ) )
    )
)

I use ISCROSSFILTERED because my regional selection is done through a map visual, not a slicer.

The whole measure is not the most elegant solution at the moment, but I can now work to integrate the other parts.

[amitchandak]

When you simply use

RANKX ( ALLSELECTED ( Reps[INDUSTRY REP] ), 'Actual'[YTD 19] )

Will, it will not give the expected result. As the measure will not rank beyond 19 ?

[Stemar_Aubert]

@amitchandak  Hi,

The switch wrapping the ranking formula doesn't impact the issue. The problem is simply that I must "cut" the top 3 results, but also have the RANKX formula calculate the set based on regionaly-filtred context.

Thanks