Hi,
I am using a table calculation to calculate the difference in revenue from one month to the next (i.e. July - August). If a store has the same revenue from one month to the next, I consider it 'No change', if the revenue in August is greater than in July I want to consider it 'Above' and if the revenue in August is less then in July I want to consider it below.
In the end I just want to be able to count the distinct stores that are 'No Change', 'Above' or 'Below'. Just one number, to indicate the distinct store counts.
I am having trouble just getting the single number. Any suggestions? I have attached the workbook.
Thanks!
ML
Hi Lisa,
see the below image, it fulfill what you looking for:
Th achive that we created 4 calsulated fields and display them as text
The first identify the relation
[State]
if FIRST() = 0 then 'First'
elseif ZN(SUM([Revenue])) = LOOKUP(ZN(SUM([Revenue])), -1) then 'Equal'
elseif ZN(SUM([Revenue])) > LOOKUP(ZN(SUM([Revenue])), -1) then 'Above'
else 'below'
end
and then 3 to calculate the count as follow
[First Count]
WINDOW_SUM(if [State] = 'First' then 1 end)
[Below Count]
WINDOW_SUM(if [State] = 'below' then 1 end)
[Above Count]
WINDOW_SUM(if [State] = 'Above' then 1 end)
I made the calculation on table down, you can make it pan down if you want
Note:
if you found this correct please mark it as correct answer or as helpful post.
Best Regards
