topic Re: Sum Total issue in DAX Commands and Tips

Sum Total issue

Allan_Zeng — Thu, 23 Feb 2023 08:18:39 GMT

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!

Re: Sum Total issue

MAwwad — Thu, 23 Feb 2023 09:20:46 GMT

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.

Re: Sum Total issue

Uspace87 — Thu, 23 Feb 2023 09:25:40 GMT

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

Re: Sum Total issue

Allan_Zeng — Wed, 01 Mar 2023 02:38:04 GMT

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!

Re: Sum Total issue

Allan_Zeng — Wed, 01 Mar 2023 02:39:46 GMT

Thank you for your reminder.

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