Forum Discussion
danimelv
4 years agoFrequent Visitor
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...
- 4 years ago
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]) ) )
danimelv
4 years agoFrequent Visitor
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. 🙂