Sum Total issue

[Allan_Zeng]

Hello Team,

I met an issue about sum need your support.

The data as below(Data is from database, and original data type is float):

Their total shows as below instead of 0. and below result will be treated as <0 but not =0.

May I know how to settle to show the sum as 0?

Thank you in advance!

[Uspace87]

@Allan_Zeng  have you tried to format the column with zero decimal places

[Allan_Zeng]

Thank you for your reminder.

I found change the data type as "Fixed decimal number" can also fix the issue.

[MAwwad · Accepted solution]

This issue could be caused by floating-point precision errors. When adding or subtracting numbers with many decimal places, the result may not be exactly what you expect due to the limited precision of floating-point numbers.

To fix this issue, you can use the ROUND function to round the numbers to a certain number of decimal places before summing them. For example, if you want to round the numbers to two decimal places, you can use the following formula:

Total = ROUND(SUM('Table'[Value]), 2)

This will round each number in the "Value" column to two decimal places before summing them, which should eliminate any floating-point precision errors.

Alternatively, you can use the DECIMAL function to convert the numbers to a fixed-point decimal format with a specific number of decimal places. For example, if you want to use four decimal places, you can use the following formula:

Total = SUM(DECIMAL('Table'[Value], 18, 4))

This will convert each number in the "Value" column to a fixed-point decimal format with 18 total digits and 4 decimal places, and then sum them. This should also eliminate any floating-point precision errors.

I hope this helps! Let me know if you have any further questions.

[Allan_Zeng]

Thank you for your solution!

The fomular Total = ROUND(SUM('Table'[Value]), 2) can fix the issue.

While the following fomular shown below error.

Total = SUM(DECIMAL('Table'[Value], 18, 4))

And I found change the data type as "Fixed decimal number" can also fix the issue.

Thank you again!