Error : Group By adding additional numbers

[Khomotjo]

Hello Everyone,

I  am trying to summarise data using group by but for some odd reason it keeps adding addional numbers :

This  are the original figures :

OrderValue
15860
7930
7930
7930
7930
27982.5
8414.83
16829.66
12937.2
6468.6
6468.6

This is the result after using Group By in Query :

I have checked and confirmed the lengths of the source data in excel  and none of the original numbers  have additional zeroes.

[Nasif_Azam]

Hey @Khomotjo ,

This issue stems from floating-point arithmetic precision in Power Query (Power BI or Excel).

Why It's Happening:

Power Query uses binary floating-point arithmetic, which can sometimes result in small rounding errors. When you use Group By → Sum, it internally adds decimal values with this floating-point logic, which can cause results like:

7930 + 7930 + 7930 + 7930 = 31720 → shows as 31719.999999999996 or 31720.000000000004

This is why your sum becomes 126681.39000000001 instead of 126681.39

How to Fix It:

Option 1: Round After Grouping

After your Group By step:

1. Add a new column using the Round function:

   • Go to Add Column → Custom Column

   • Use:

     Number.Round([Sum], 2)

   • Or use Number.Round([YourColumn], 2, RoundingMode.AwayFromZero) for consistent rounding.

2. Remove the original Sum column if you don’t need it.

Option 2: Round Before Grouping (not always recommended)

If you want to be cautious, you could round each number to 2 decimal places before the Group By step:

Table.TransformColumns(Source, {{"OrderValue", each Number.Round(_, 2)}})

This can cause incorrect results in certain scenarios (e.g., if many small rounding errors accumulate).

Best Practice: Always apply rounding after aggregation (e.g., Sum) to avoid floating-point issues in Power Query.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[mdaatifraza5556]

Hi @Khomotjo 

Are following these steps"

Suppose grouping the Order value by Customer then 

If this answers your questions, kindly accept it as a solution and give kudos.

[Cookistador · Accepted solution]

Hi @Khomotjo 

This issue is caused by how Power BI (Power Query / M) handles floating-point numbers 

It's pretty weird that your sum, in string type and not text, so can you first try to replace the type by Fixed decimal number

If it is not working

In power Query, 

can you try to replace Sum = List.Sum([OrderValue]) by Sum = Number.Round(List.Sum([OrderValue]), 2)