Strange behaviour when deducting decimal numbers - precision issues?

[TBurkert]

Hi,

I hope I'm not going crazy - it seems that Power BI is adding some random junk to the very far decimal positions when doing some basic math operations.

Let's do some basic test:

 Column = (1-0.5-0.5)=0

This produces TRUE, as expected.

Column = (1-0.6-0.4)=0

Also TRUE

Column = (1-0.7-0.3)=0

FALSE

I noticed that when comparing a KPI threshold of 0.05 to a number which was basically (1-19/20), which is 1/20, which is exactly 0.05.

However, that does not seem to be the case in Power BI:

(1-0.95)>0.05000000000000004093

TRUE

(1-0.95)>0.05000000000000004094

FALSE

Note that I'm working only with numbers entered directly here, there are no columns involved, whose data type settings could be at play.

Is there an explanation for this?

I have tried this in multiple projects.

I'm using Microsoft Power BI Desktop (Optimized for Power BI Report Server - August 2018)

Version: 2.61.5192.641 32-bit (August 2018) 

Thank you!

[TBurkert]

Hi,

I hope I'm not going crazy - it seems that Power BI is adding some random junk to the very far decimal positions when doing some basic math operations.

Let's do some basic test:

 Column = (1-0.5-0.5)=0

This produces TRUE, as expected.

Column = (1-0.6-0.4)=0

Also TRUE

Column = (1-0.7-0.3)=0

FALSE

I noticed that when comparing a KPI threshold of 0.05 to a number which was basically (1-19/20), which is 1/20, which is exactly 0.05.

However, that does not seem to be the case in Power BI:

(1-0.95)>0.05

TRUE

(1-0.95)>0.05000000000000004093

TRUE

(1-0.95)>0.05000000000000004094

FALSE

Note that I'm working only with numbers entered directly here, there are no columns involved, whose data type settings could be at play.

Is there an explanation for this?

I have tried this in multiple projects.

I'm using Microsoft Power BI Desktop (Optimized for Power BI Report Server - August 2018)

Version: 2.61.5192.641 32-bit (August 2018) 

Thank you!

[TBurkert]

Also note that parentheses have no (unexpected) effect on the outcome.

[v-qiuyu-msft]

Hi @TBurkert,

I have reported this issue internally: CRI 94574136. Will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @TBurkert,

I got information below: 

"This is by design and here is the explanation: The operations listed in the incident are over decimal numbers. Power BI follows the IEEE Standard for Floating-Point Arithmetic (IEEE 754) in its support of decimal numbers as double-precision floating point numbers. Double-precision floating point numbers cannot represent all real numbers precisely in computer hardware therefore math calculations involving imprecisely represented real numbers can have rounding errors in high precision. To ensure comparison operations to return expected results, the user can either change the original column data type to fixed decimal numbers or modify the calculations to compensate for the lack of precision of representation of real numbers. For example, it's common practice for programmers to write ABS(a - b) < 1E-6 instead a = b when a and b are double-precision floating point numbers."

Best Regards,
Qiuyun Yu