I am trying to count the distinct combination of country&family where the rolling 4 weeks avg is less than the rolling 52 week avg. My data is at a lower level than what I am trying to count so i have created variables to accommodate.
v4WK_CountryFamily --> this works properly and returns the correct AVG sales per day over the past 4 weeks at the aggregated country/family level
AVG({$<SalesType=>}AGGR(Sum({$<[Rolling 4WK Flag]={1},SalesType=>}[USD Sales Amount]),[Ship Date],[Country Name],Family))
v52WK_CountryFamily --> this works properly and returns the correct AVG sales per day over the past 52 weeks at the aggregated country/family level
AVG({$<SalesType=>}AGGR(Sum({$<[Rolling 52WK Flag]={1},SalesType=>}[USD Sales Amount]),[Ship Date],[Country Name],Family))
Now I want to create a KPI that counts the number of CountryFamilies where the 4wk avg < 52wk avg....been trying something like this but it isn't giving the correct answer. Any suggestions?
Count(Distinct {< [USD Sales Amount] = {"=$(v4WK_CountryFamily) < $(=v52WK_CountryFamily)"}>} "Country Name"&Family )
