How to calculate percent and avoid divide by zero?

[croberts21]

I'm still pretty new to PBI. I have Free PBI Desktop v2.100.1182 (64 bit free version Dec 2021) on Windows 10. I use an ODBC driver to connect to a Postgresql database server.

I want to make a measure in a table viz which will calculate a percent based on the sum of a row. However, sometimes the numerator sums to zero, blank or null, sometimes the denominator sums to zero, blank or null. Right now my measure is: 

JobCost[aaEstCostSum] / JobCost[aaActCostSum]

And some of the percents are "Infinite" or "NaN". Like in this screenshot. (Using fake data.)

Basically I would like to do this in the measure but I don't know how.

PctOfEstCost = If(INUMBER(JobCost[aaEstCostSum])=True and ISNUMBER(JobCost[aaActCostSum])=True, JobCost[aaEstCostSum] / JobCost[aaActCostSum], BLANK)

I've already searched and read some pages, the DAX reference, and some videos. 

So I would need a compound IF statement but the DAX documentation doesn't given an example of a compound IF statement.

Thanks!

[PaulDBrown · Accepted solution]

Use the DIVIDE function

[croberts21]

Wow it works! I'm getting learneded! 🙂

Here's how I did it: 

PctOfEstCost = DIVIDE(JobCost[aaEstCostSum], JobCost[aaActCostSum],"")

The third parameter is the alt result which is a blank string.