Measure in table that considers an if statement, divide two column sums

[granade1]

My mult. of W2 column is working with this measure:

Mult. of W2 = (SUM(Merge2[InvoiceAmnt]) / SUM(Merge2[Cost+Exp Total])/100)

but I need to include an if statement for when expenses are = 0. to avoid infinity.

So something like:

if expenses = 0 then SUM(Merge2[InvoiceAmnt]) / SUM(Merge2[Cost-Time])/100

ELSE (SUM(Merge2[InvoiceAmnt]) / SUM(Merge2[Cost+Exp Total])/100)                             <-my original measure

END

Still learning DAX and can't seem to figure this out. thanks!

[Anonymous · Accepted solution]

First of all, mate, it's not a measure you're working with but a calculated column. The stuff you think is a measure is an in-column expression.

Mult. of W2 =
var __invoiceSum = SUM( Merge2[InvoiceAmnt] )
var __costMinusTimeSum = SUM( Merge2[Cost-Time] )
var __costPlusExpSum = SUM( Merge2[Cost+Exp Total] )
var __exprWhenExpensesEquals0 =
	DIVIDE ( 
		__invoiceSum,
		__costMinusTimeSum * 100
	)
var __exprWhenExpensesNotEquals0 =
	divide (
		__invoiceSum,
		__costPlusExpSum * 100
	)
var __result =
	if (
		Merge2[Expenses] = 0,
		__exprWhenExpensesEquals0,
		__exprWhenExpensesNotEquals0
	)
return
	__result

Best

Darek