cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rmcvicar
Helper I
Helper I

Infinity and Divide; and figuring out DAX

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

1 ACCEPTED SOLUTION

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

View solution in original post

7 REPLIES 7
AlexisOlson
Super User
Super User

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
)

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)

2021-10-21_10-20-26.png

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.

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.

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

View solution in original post

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.

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.