topic Re: RANKX how to exclude certain rows from the context in DAX Commands and Tips

RANKX how to exclude certain rows from the context

Anonymous — Wed, 25 May 2022 08:36:36 GMT

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

Re: RANKX how to exclude certain rows from the context

tamerj1 — Wed, 25 May 2022 08:43:52 GMT

HI Anonymous
Please try

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

Re: RANKX how to exclude certain rows from the context

Anonymous — Wed, 25 May 2022 08:56:37 GMT

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

Re: RANKX how to exclude certain rows from the context

tamerj1 — Wed, 25 May 2022 09:32:17 GMT

Anonymous
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?

Re: RANKX how to exclude certain rows from the context

Anonymous — Wed, 25 May 2022 09:40:03 GMT

Yes excatly

Re: RANKX how to exclude certain rows from the context

Anonymous — Wed, 25 May 2022 09:49:12 GMT

Yes exactly

Re: RANKX how to exclude certain rows from the context

tamerj1 — Wed, 25 May 2022 09:58:15 GMT

Anonymous
Please try

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

Re: RANKX how to exclude certain rows from the context

ribisht17 — Wed, 25 May 2022 10:22:34 GMT

Anonymous

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

Re: RANKX how to exclude certain rows from the context

Anonymous — Tue, 31 May 2022 06:44:39 GMT

Hi Anonymous ,

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