Forum Discussion
Filter table and then ranking
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 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
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] )
4 Replies
Eric_ZhangMicrosoft Employee
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
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
Community Champion
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))
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?
vanessafvg Thanks for your input. Maybe I am doing something wrong as I can't make it work.??
/ Soren
