I think I am running into an error in how I have chosen to aggregate my columns in a group by statement.
I am working out Margin % and grouping into months using a case statement like so:
SUM(CASE WHEN MONTH(DateProcessed) = 1 AND Revenus <> 0 THEN (Revenue - Cost) / Revenue ELSE 0 END AS [January]
So my equation ends up as:
SUM( (Revenue - Cost) / Revenue )
Which in months with multiple transactions gives me the wrong value
I am sure my equation needs to be:
SUM(Revenue - Cost) / SUM(Revenue)
But I cannot format my case statement like that. How would you work out this sort of equation?