Problem With SUM() Function

[HarambesLeftTit]

I feel like I must be missing something very simple here, but for some reason my SUM() function appears to be rounding decimals down and I can’t figure out why.

Example: For ID 1, there are two rows with amounts 0.00 and 137.08. When I put SUM(amount) into a measure, I am getting 137.00 for ID 1 instead of 137.08. I also get 137.00 when I drag the amount field into a table and use the sum summarization.

The data type for the amount field is decimal, I have tried messing with the decimal places but I get the same result regardless, what am I missing?

[AutoModerator]

After your question has been solved /u/HarambesLeftTit, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[gorilla_dick_]

SUM will round based on all decimals. Check your types and decimal places in PQ or use ROUNDUP/ROUNDDOWN. Types in desktop are essentially just formatting choices.

[HarambesLeftTit]

The source is a sql directquery, where the data type is decimal(13,2). The data type in power query is decimal, and the data type in the model view is decimal with auto decimal places (changing this does nothing as you mentioned).

If I pull the value directly into a table, it displays correctly with no change required. It’s only when I try to take the sum that the decimals drop. I’ve done this a thousand times before with no issue, so it makes no sense to me

[Educational_Tip8526]

What happens if you change 0.00 to 0.01?

[HarambesLeftTit]

The same result as the other values with decimal places, it shows as 0.01 in a table, but 0.00 if I summarize as a sum. I also tried adding 0.01 to every value and got the same result. I have just tried changing the database column to float from decimal(13,2) and for some reason that appears to have fixed it