Hi all,
Following this video of Alberto Ferrari i managed to write the code for a dynamic "others". So far, so good. The problem comes when I want to calculate differences vs the last year, as I need to recalculate for the last year the sum of the elements that are part of the rest in the current year. I wrote this, but the "others" row is not showing the right value. Any ideas?
Sales LY =
var TotalSales=
CALCULATE([Sales],REMOVEFILTERS('Countrytable'[Country]),SAMEPERIODLASTYEAR(Caleandar[Date]))
RETURN
IF(ISINSCOPE('Countrytable'[Country]),
var countryrank= 'TopN'[Valor TopN]
var SelectedOthers = SELECTEDVALUE('Countrytable'[Country]) = "Others"
var Tablecountries=ADDCOLUMNS(ALLSELECTED('Countrytable'[Country]),"@sales",CALCULATE([Sales],SAMEPERIODLASTYEAR(Caleandar[Date])))
var Topcountries = TOPN(countryrank,Tablecountries,[@sales],DESC)
var CountryTop = Sumx(countryrank,[@sales])
var Result = if(SelectedOthers,TotalSales-CountryTop ,CALCULATE([[Sales]l],SAMEPERIODLASTYEAR(Caleandar[Date])))
RETURN Result,TotalSales)
All values that are inscope are correctly calcualted, but not the "others". When doing (TotalSales-CountryTop), the script is only able to compute the ContryTop value. Instead, the total sales appear 0. The total (out of scope) works fine, so i guess it's regarding the filter context withing the ISINSCOPE function. Help!
