Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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?

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.