Forum Discussion
Sum Total issue
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!
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.
4 Replies
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.
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!
Thank you for your reminder.
I found change the data type as "Fixed decimal number" can also fix the issue.
