Forum Discussion
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 Countries and the rest of countries:
However, when the users click on "Others" I woould like to retrieve the other countries with the amount, that is, Poland, Portugal and Spain with 9,8,5 Total respectively:
I have created an Unlinked table in order to make a virtual relation and create a measure to calculate the total for this countries. Unfortunately, I'm not able to match the Country from my Table with my new Unlinked table:
DEFINE
VAR Tab1 =
ADDCOLUMNS (
SUMMARIZE ( CountryOthers, CountryOthers[Ctry] ),
"Actuals", [Total],
"@Others", [Amount Oth]
)
VAR Tab2 =
FILTER ( Tab1, AND ( [Actuals] <> BLANK (), [@Others] = BLANK () ) )
VAR Tab3 =
SUMMARIZE ( Tab2, [Ctry] )
EVALUATE
ADDCOLUMNS (
SUMMARIZE ( Unlink, Unlink[Ctry] ),
"TotalOthers",
SUMX (
Tab3,
VAR CurrCtry = [Ctry]
RETURN
CALCULATE ( [Total], CountryOthers[Ctry] = CurrCtry )
)
)
Any Idea how can achieve this? Maybe is there a better way to approach this problem?
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]) ) )
9 Replies
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 🙂
Hi TheoC Thanks for your response. The only problem I can see from your solutions is, What if the Top N is dynamic? e.g, If I have a date filter, the Top N could vary depending on the date selected. That's what I've been thinking on doing with an unlinked table and not creating a physical relation. 🙂
TheoCI have tried but as I mentioned, it did not work for me. Maybe, if you could attach a sample file it would be easier (for sure I am doing something wrong). Anyway, I have found the solution via formula, so I put it here if someone has the same requierment:
Amount_Unlink = VAR Tab1 = ADDCOLUMNS ( VALUES ( CountryOthers[Ctry] ), "@Others", [Amount Oth], "Actuals", [Total] ) VAR Tab2 = FILTER ( Tab1, AND ( [Actuals] <> BLANK (), [@Others] = BLANK () ) ) VAR Tab3 = SUMMARIZE ( Tab2, [Ctry] ) Return SUMX ( INTERSECT ( Tab3, Unlink ), VAR _curr = [Ctry] RETURN CALCULATE ( [Total], Tabla[Ctry] = _Curr ) )
