Filter table and then ranking

[SAM190370]

I would like to ranking a table.  This works fine with rankx.  However I would like to filter the table before making the ranking. However it seems like formula ignore the filtering. 

1) First you see the data

2) Ranking withour filtering

3) Ranking without Denmark

How should the measure look like? I have tried this one but it seems to sum up top 3 to 1000 - just ignorering the new ranking. 

Top3WithoutDenmark:=CALCULATE('Data'[Amount];FILTER(Data;Data[Country]<>"Denmark");FILTER(Data;RANKX(ALL(Data);[Amount])<=3))

It seems like rank ignore any filter before to the ranking.

Any suggestions?

/ Søren

[Eric_Zhang · Accepted solution]

---

@SAM190370 wrote:

I would like to ranking a table.  This works fine with rankx.  However I would like to filter the table before making the ranking. However it seems like formula ignore the filtering. 

1) First you see the data

2) Ranking withour filtering

3) Ranking without Denmark

How should the measure look like? I have tried this one but it seems to sum up top 3 to 1000 - just ignorering the new ranking. 

Top3WithoutDenmark:=CALCULATE('Data'[Amount];FILTER(Data;Data[Country]<>"Denmark");FILTER(Data;RANKX(ALL(Data);[Amount])<=3))

It seems like rank ignore any filter before to the ranking.

Any suggestions?

/ Søren

---

@SAM190370

Maybe you can try a measure with more flexibility. Check more details in the attached pbix.

Top3ofAnySelectedCountry =
VAR TBL =
    SUMMARIZE ( Data, Data[Country], "sumtotal", SUM ( Data[Amount] ) )
VAR RNK_TBL =
    ADDCOLUMNS ( TBL, "RANK", RANKX ( TBL, [sumtotal],, DESC, DENSE ) )
RETURN
    SUMX ( FILTER ( RNK_TBL, [RANK] <= 3 ), [sumtotal] )

[vanessafvg]

@SAM190370

i think the issue is to do with your all statement, all removes the filter context, try allselected

Top3WithoutDenmark:=CALCULATE('Data'[Amount];FILTER(Data;Data[Country]<>"Denmark");FILTER(Data;RANKX(ALLSELECTED(Data);[Amount])<=3))

[SAM190370]

@vanessafvg Thanks for your input.  Maybe I am doing something wrong as I can't make it work.??

/ Soren

[SAM190370]

@Eric_Zhang This is definitely a more flexible solution and it actually works very well.  However, in my real world situation I have several "countries" to select from and would like to deselect one.

Do you have any idea how I can write in the filter directly in the measure to make it work?