RANKX how to exclude certain rows from the context

[Antmkjr]

I am applying the below RANKX formula, 

Rank = RANKX(ALL('Table'[Continent]), CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Continent])))

To get a Rank based on Continent.

I want to exclude certain countries, say France in this example, how to restructure the formula for this.

India	100	1	Asia
Pakistan	50	1	Asia
China	90	1	Asia
Australia	85	2	Australia
France	70	3	Europe

[v-jayw-msft · Accepted solution]

Hi @Antmkjr ,

Please check the formula.

Column = 
VAR rank_ =
    RANKX (
        'Table',
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Continent] = EARLIER ( 'Table'[Continent] )
                    && 'Table'[Country] <> "France"
            )
        ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( 'Table'[Country] = "France", BLANK (), rank_ )

Best Regards,

Jay

[ribisht17]

@Antmkjr 

First >>

I can remove Sales from the context itself whenever I have France, In my case, it is "d"

Column = RANKX(FILTER( all(Sheet3),Sheet3[Category]=EARLIER(Sheet3[Category]) ),(IF(Sheet3[Sub Category]="d","",Sheet3[Sales])),,ASC,Dense)

By doing this I am ensuring that it will come at the bottom of my Rankings within a Category(NULL)

New Rank = IF(Sheet3[Sub Category]="d",0,Sheet3[Column])

You can exclude these 0s in the main dashboard as needed, also Ranking will not affect, it will just exclude d/France

Regards,

Ritesh

[Antmkjr]

Yes exactly

[Antmkjr]

Yes excatly

[tamerj1]

@Antmkjr 
Please try

Rank =
RANKX (
    ALL ( 'Table'[Continent] ),
    CALCULATE (
        SUMX (
            CALCULATETABLE (
                FILTER ( 'Table', NOT ( 'Table'[Country] IN { "India", "China" } ) ),
                ALLEXCEPT ( 'Table', 'Table'[Continent] )
            ),
            'Table'[Sales]
        )
    )
)

[tamerj1]

HI @Antmkjr 
Please try

Rank =
RANKX (
    FILTER (
        ALL ( 'Table'[Continent] ),
        CALCULATE ( VALUES ( 'Table'[Country] ) ) <> "France"
    ),
    CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Continent] ) )
)

[Antmkjr]

It is wrongly calculating Rank of France here as 2, as I mentioned, I want to exclude France from this Ranking.

[tamerj1]

@Antmkjr 
Just to properly understand your rquirement. If we exclude India and China for example, then Asia will be ranked 3? Is this what you want?