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([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

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

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

7 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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
)
    • 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
        Icon for Super User rankSuper 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.