Calculate average of last 3 weeks If revenue is Null

Hi All,

I have tried with the Table calculation to get the Average of Last 3 weeks revenue, but when i tried to embed into If Statement, i receive the below error

I am receiving the following error, “cannot mix aggregate and non-aggregate comparisons or results  in 'if expressions” error"...

I am aware this is because of using table calculation in If Condition.

Table Calculation:

[Last 3 weeks revenue]: (ZN(SUM([Revenue])) + LOOKUP(ZN(SUM([Revenue])), -1) + LOOKUP(ZN(SUM([Revenue])), -2))/3

[Revenue] :

If ISNULL([Revenue])

then [Last 3 weeks Revenue]

ELSE [Actual Revenue]

END

Can any one help me out with a solution or Workaround on this.

Thank you,

Aravind