
1. Re: Newbie  Question similar to countif in excel
Bobby Souza Dec 28, 2016 10:18 AM (in response to Fábio Rocha)Hi Fábio,
Great question. See attached for a workbook.
Basically, we want to create a FIXED formula to give us the total sales per store per week. We can achieve this by using the formula:
IF {FIXED [Store],[Week]:SUM([Sales])}>20 then 1 else 0 END
Note: The FIXED formula is great to use when you'd like to aggregate data at certain levels/dimensions.
Now that we have those weeks with sales > 20 per store (labeled [Relevant Weeks]), we can create another field using [Relevant Weeks] (formula above) to get the average.
SUM(IF [Relevant Weeks] = 1 THEN [Sales] ELSE 0 END)/SUM([Relevant Weeks])
Hope this helps. Cheers!

Book1.twbx 21.6 KB


2. Re: Newbie  Question similar to countif in excel
Fábio Rocha Dec 29, 2016 2:14 AM (in response to Fábio Rocha)Hi Bobby,
Yeah, that worked! Thanks a lot! I managed to get a little more understanding about FIXED as well
Just have an issue regarding this being a LOD expression and having 3/4 filters needed to be applied prior to this calc. Just turned them into context, but I believe this will have an impact on performance as I need to show them on the dashboard so the reader can "customize" the view... I'm gonna wrap this up and compare the difference in performance.
Thanks once again