Infinity and Divide; and figuring out DAX

[rmcvicar]

I am attempting to get 0 (zero) instead of an 2422361554944000

Any Idea on a function that would return a proper division when not Blank or zero?

Current Measure

* Accuracy % = IFERROR(DIVIDE(IF([Sales Invoice Line Net Weight] <0,0,[Sales Invoice Line Net Weight]), IF([Forecast Lbs] <0, BLANK() ,[Forecast Lbs]),0), BLANK())

Forecast LBS = 0.00
Sales Inv Net Wght = 70500.00
Accuracy % = 242236155494400000%  (Value attempting to avoid)
Infinity Value = 2.42236E+15  (Value attempting to avoid)

Thanks for your input

[AlexisOlson]

I'm guessing [Forcast LBS] isn't exactly zero but rather something like 2.91E-11, so dividing by this very small number yields a very big number.

Try rounding that measure to a couple of decimal places to avoid this sort of thing.

Accuracy % =
DIVIDE (
    MAX ( [Sales Invoice Line Net Weight], 0 ),
    MAX ( ROUND ( [Forecast Lbs], 2 ), 0 ),
    0
)

[rmcvicar]

Thank you for the reply, however I get the same results: 242236155494400000% or 2422361554944000

This infinity, right?

So if results are infinity due to a DIVIDE and the Denominator is something like this 2422361554944000 or zero because that is what [Forecast LBS]  is  0.00.  

Formula (Measure) used: 

* Accuracy % = DIVIDE( MAX([Sales Invoice Line Net Weight], 0), MAX([Forecast Lbs],0) ,0 )

DIVIDE( 70500.00 , 0.00, 0)

Forecast LBS = 0.00
Sales Inv Net Wght = 70500.00
Accuracy % = 242236155494400000%  (Value attempting to avoid)
Infinity Value = 2.42236E+15  (Value attempting to avoid)

[AlexisOlson]

This is not infinity. The DIVIDE function exists to handle division by zero but won't recognize the denominator as zero if it's not actually a zero but something very close to zero.

If you type in Accuracy % = DIVIDE( 70500.00 , 0.00, 0) as the definition for your measure, you will not get the huge result you are currently seeing.

Please try a version with ROUND like I suggested.

[rmcvicar]

Thank you and yes I did do a test with just the numbers and not pulling from the dataset.  And yes it responds as you say.  However when using the dataset I get the infinity looking number as I have discribed.  I created two seperate measures: one for the Numerator and one for the denominator and both of those show 70500.00 & 0.00 respectively.  However if I use the

DIVIDE([*** Sal Inv Nt Wght], [*** Forecast Lbs],0) for example I get the 2422361554944000

If I use the two original measures formula 

DIVIDE([Sales Invoice Line Net Weight], [Forecast Lbs] ,0 ) I get 2422361554944000.  so regardless I get this large or infinity number instead of 0 (zero).  Any other thoughts?  I have tried this as a Measure and as a Calculated Column too.

[AlexisOlson · Accepted solution]

Is there a reason you keep ignoring my suggestion to ROUND the denominator?

[rmcvicar]

Well Alexis thank you very much that was the trick.  I was stuck on infinity and / or why this large number when the denominator was really 0.00.  But by adding the ROUND to both my measure producing this I now get the right results.  Again thank you very much.

[AlexisOlson]

Yep. This issue is that it wasn't really zero, even if it logically should have been. Possibly a floating-point error.