Forum Discussion
Solved
Dynamic Top N + Other filtering
Hi All, I've been playing a while but I can't get what I want, so I am asking for help, pls Attached you can find the PBIX File . I have a measure that returns the Total Amount of Top 3 Countrie...
Hi TheoC
Ofc I've tried, but maybe it would be easier if you share your solution (for sure I'm doing something wrong but It did not work as spected). Anyway, I've found the solution to the requirement so I'm sharing it in case anybody has the same requirement:
VAR OthersSel = SELECTEDVALUE( CountryOthers[Ctry] ) = "Others" VAR Tab1 = ADDCOLUMNS ( ALL ( CountryOthers[Ctry] ), "@Others", [Amount Oth], "Actuals", [Total] ) VAR Tab2 = FILTER ( Tab1, AND ( [Actuals] <> BLANK (), [@Others] = BLANK () ) ) VAR Tab3 = SUMMARIZE ( Tab2, [Ctry] ) VAR Result = SUMX ( INTERSECT ( Tab3, Unlink ), VAR _curr = [Ctry] RETURN CALCULATE ( [Total], Tabla[Ctry] = _Curr ) ) Return IF( OthersSel, Result, CALCULATE( [Total], TREATAS(VALUES(Unlink[Ctry]), CountryOthers[Ctry]) ) )
Edited:
Hi danimelv
I've done something similar but taken a bit more of a "two step approach".
1. Create a new table using your Country names:
New Table = SUMMARIZE ( 'Table' , 'Table'[Country Name] )
2. Create Calculated Column in New Table to rank / group:
New Column1 =
VAR _1 = RANKX ( ALL ( 'Table' ) , [Your Measure] , , DESC )
RETURN
IF ( _1 <= 3 , 'New Table'[Country Name] , "Other" )
Ensure to create a 1 to many relationship from New Table[Country Name] to your original table and you can then drag the New Column1 into the Matrix or Table visual you're using.
Hope this helps!
Theo 🙂