Measure dividing by 0 giving strange result

[Anonymous]

Hi all, 

I've got some strange behaviour from a measure. I'm dividing Jobs Count by Hrs Sold, but I've gotten an erroneous result. Seen in the table below this has resulted in a value of 72,057,594,037,927,940. 

I've written the measure out two different ways but they both end with this result. Any help as to why this is happening would be greatly appreciated.

The following are the two measures that I'm using : 

Avg Hrs Per Job Sold =

var WIP = [WIP COUNT]

var SoldHrs = SUM(vPowerBI_Service_WarrantyEfficiency[WarrantySold])

var result = DIVIDE(WIP,SoldHrs,0)

return result

and

AvgHrsPerJobSold =

    VAR WIPNumberCount = [WIP COUNT]

    VAR AllowedTimeSum = SUM(vPowerBI_Service_WarrantyEfficiency[WarrantySold])

    RETURN

        IF(WIPNumberCount = 0 || AllowedTimeSum = 0, 0, DIVIDE(WIPNumberCount, AllowedTimeSum, 0))

[Anonymous]

Hi @Anonymous 

@rsbin Thank you very much for your prompt reply. Allow me to share some content here.

The problem you are experiencing appears to be a case of overflow error or divide by zero error.

This can happen when the denominator in a division operation is zero or very close to zero, which can cause the result to spike to extremely high numbers.

You can try the method:

Check if the value is zero or close to zero.

Check the data type. If they are integers, consider whether they need to be converted to a decimal or floating-point data type in order to properly handle fractions.

You can use the ISERROR function to check for errors and handle them accordingly.

Avg Hrs Per Job Sold =
VAR WIP = [WIP COUNT]
VAR SoldHrs = SUM(vPowerBI_Service_WarrantyEfficiency[WarrantySold])
VAR result = IF(
    ISERROR(DIVIDE(WIP, SoldHrs)),
    0,
    DIVIDE(WIP, SoldHrs, 0)
)
RETURN result

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thankyou both for replying, I have tried the Dax fix you provided but it still results in the same result. Would you happen to have another fix that I could try?

[Anonymous]

Hi @Anonymous 

Can you share some virtual data? This helps to help you solve the problem. Be careful to delete sensitive data.

[rsbin · Accepted solution]

@Anonymous ,

Appears you have a rounding issue with your Hours Sold.

Actual Hours Sold is not 0.  The actual value is

0.000000000000000027756

You are just displaying it as 2 decimal places.

The way to correct for this is to use the ROUND function to calculate Hours Sold.

Hope this helps.

Regards,

[Anonymous]

How would I integrate this into my measure? 

[Anonymous]

The following adaptation to my measure fixed this issue :

Avg Hrs Per Job Sold =

var WIP = [WIP Count]

var SoldHrs = ROUND(SUM(vPowerBI_Service_WarrantyEfficiency[WarrantySold]), 2)

var result = DIVIDE(WIP,SoldHrs,0)

return result

Thankyou both for your help !

[rsbin]

@Anonymous ,

Glad you were finally able to resolve this issue.

Kind Regards,