Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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(...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
AlexisOlson
Super User
4 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      AlexisOlson
      Super User
      4 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.