I have this calculation that gives me which store is above and below based on revenue:
IF SUM( [NET_REVENUE]) > WINDOW_AVG(SUM([NET_REVENUE])) THEN "Above average"
ELSEIF SUM([NET_REVENUE]) < WINDOW_AVG(SUM([NET_REVENUE])) THEN "Below average"
What I need to do is take this and count the total stores in each category. So I wrote this
IF [AboveOrBelowAverage] = "Above average" then COUNTD([CLINIC_EXT_ID] ) end
But it isn't working the way I thought it would.
Oh and I tried this Boolean too but to no avail: AVG([NET_REVENUE]))) > TOTAL(AVG([NET_REVENUE]))
I know I am missing something simple, and I am going to hate myself when someone points out what it is, but for the life of me I can't see it.
I would post the workbook, but it has client data in it and the extract is huge.
Maybe try something like:
WINDOW_SUM(IF [AboveOrBelowAverage] = "Above average" THEN 1 ELSE 0 END)