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]) ) )
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 )
)