IF - THEN Calculation

[Rsanjuan]

I have two columns....Proposed Budget and Proposed Costs.  I am trying to calculate the gross margin % using this formula:

(Proposed Budget - Proposed Costs) / Proposed Budget

However, there are rows where Proposed Budget and Proposed Costs have values of 0.  And when 0 is divided by 0, I am getting a NaN error.

What would be the correct syntax to only calculate this formula ONLY when Proposed Budget > 0?

Thanks!

[Vvelarde · Accepted solution]

@Rsanjuan

hi, try using Divide Function

Result=Divide(Table1[Proposed Budget]- Table1[Proposed Costs], Table1[Proposed Budget],Blank())

[geeb · Accepted solution]

You can also use IF([Proposed Budget] > 0, ([Proposed Budget] -[Proposed Costs]) / [Proposed Budget]). This will return a blank if the condition is false.

The solution above is nice and clean though.

[Vvelarde · Accepted solution]

@Rsanjuan

hi, try using Divide Function

Result=Divide(Table1[Proposed Budget]- Table1[Proposed Costs], Table1[Proposed Budget],Blank())