March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
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
Solved! Go to Solution.
Is there a reason you keep ignoring my suggestion to ROUND the denominator?
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:
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)
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
91 | |
91 | |
79 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
72 | |
55 |