Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
87 | |
67 | |
49 |
User | Count |
---|---|
135 | |
112 | |
100 | |
68 | |
67 |