Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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] )
@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] )
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))
Proud to be a Super User!
@vanessafvg Thanks for your input. Maybe I am doing something wrong as I can't make it work.??
/ Soren
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |