Expect 0 get -0.000000000000043

[Anonymous]

Hi All.

I´m geting extrem values sometimes on a Margin% measure, exampel: 750951781615971100.00%
Measure: 

Margin =
DIVIDE (
( SUM ( 'sales'[Net sales] ) - SUM ( 'sales'[Cost] ) ),
SUM ( 'sales'[Net sales] ),
0
)

When trubleshooting I´ve found that  'sales'[Net sales] sometime instead of being 0 is very smal ex, -0.000000000000043.
In data source (SQL database) [Net sales] is deffined as : Decimal(20,6) and value for the Example are 00.000000 

 So when pulling in the data Power BI is sometimes turning 0.000000 into -0.000000000000043 or similar.

Does any one know what´s going wrong and how to fix it?

BR Måns

[BA_Pete · Accepted solution]

Hi @Anonymous ,

The problem is usually to do with how computers handle floating point numbers within a binary context. Tom Scott explains it beautifully here:
https://www.youtube.com/watch?v=PZRI1IfStY0 

The solution is to round off your numbers in the transformation process. For example, in Power Query, your would apply a transformation similar to this on all of your decimal columns:

Number.Round([DecimalColumn], 2)

This should force the tiny translation roundings to be truncated.

Pete

[Anonymous]

Thanks for answer, rounding solves issue with extrem results from measure. 👍
But is it really possible that a 0.000000 can turn in to  -0.000000000000043 due to floting point issue?

Most zeros in data is not turnd into super low values. Could it not be that the underlying data in this case isn´t acctualy 0.000000 but infact -0.000000000000043 rounded to 0.000000?

Of course if we find -0.000000000000043 in underlying data this could be due to floting point but I´m just oparating in PBI 

[BA_Pete]

It looks like, in your example, you're deducting sales[Cost] from sales[Net Sales] to get your Margin value. My guess would be that your [Net Sales] value is actually dead zero, but one/some/all of your [Cost] values have this floating point issue, hence why you get a negative tiny number.

It's possibly due to a calculation at source that produces the [Cost] values. For example, it may actually be calculated at source as [Total Cost] / [Unit Quantity] or similar, thus inviting a floating point issue.

Pete