Hi, everyone!
I need to count outlets that bought both of brands in a year in each month separately and in total.
Value for each brand I take from alternate states.
I tried this, but I had to use different formulas for months and total.
if (Dimensionality()=1,
SubStringCount(concat(aggr(if(Sum({$*[Brand Old]<H_IsSales={1},Year={$(=max(Year))}>}[Volume]) and
Sum({$*[Brand New]<H_IsSales={1},Year={$(=max(Year))}>}[Volume]),[Outlet_code]&'|'),[Outlet_code],Month)), '|')),
SubStringCount(concat(aggr(if(Sum({$*[Brand Old]<H_IsSales={1},Year={$(=max(Year))}>}[Volume]) and
Sum({$*[Brand New]<H_IsSales={1},Year={$(=max(Year))}>}[Volume]),[Outlet_code]&'|'),[Outlet_code])), '|'))
)
And when I use if statment, it takes a lot of time to generate my report. I need to make it faster.
Any ideas how can I make this formula simpler?
