DecimalNumber with 15 max limit not working while aggregation

[jaryszek]

Hi Guys,

i need to compare raw data agregated on visual (dax) or within power query with validation data like that:

This is Cost for 1 month: 
44666.17333394919842

as you can see it is 14 decimal places + 4 main digits = 18.

After converting this from this code to double in power qeury:

  double = Value.ReplaceType(
            each Double.From(NumberFrom.FromText(_, "en-US")),
            type function (x as text) as Double.Type
        ),

I am getting 15 digits which is max for double in power query like this:
44666,1733339492

But what is interesting while I am grouping it via power query per date the result is:
44666,173592146137
it has 12 decimal places and 5 main digits so 17 at all!!

(it is my code: 

= Table.Group(
        Source,
        {"Date"},
        {
            {
                "TotalCost",
                each List.Sum(
                    List.Transform([CostInBillingCurrency], each Double.From(_))
                ),
                type number
            }
        }
    )

Why? 
It is IEEE 754 binary operation which is figured out by power bi ? Or what? 
How to be sure that decimal places are the same when I am aggregating (summing) data?

Can anybody explan this behaviour? 

Also want to add that using visual and DAX measure it is also showing more digits than 15 for aggregated data...

Best,
Jacek

[ZhangKun · Accepted solution]

The calculation order of double-precision floating-point numbers may lead to different results because the last digit is rounded differently in the calculation. For example, the following example:

List.Transform({{-0.4, 0.1, 0.2, 0.3, -0.31, 0.1, 0.01}, {-0.4, 0.1, -0.31, 0.1, 0.01, 0.2, 0.3}}, List.Sum)

Numbers in Power Query are all treated as double-precision floating point numbers, but some functions can specify the calculation precision (double-precision floating point or fixed-digit decimals), such as the following example:

List.Transform({{-0.4, 0.1, 0.2, 0.3, -0.31, 0.1, 0.01}, {-0.4, 0.1, -0.31, 0.1, 0.01, 0.2, 0.3}}, each List.Sum(_, Precision.Decimal))

This is difficult to guarantee in DAX because the only currency types that guarantee precision are only accurate to 4 decimal places.

[Akash_Varuna]

Hi @jaryszek I think it might be related to IEEE 754 floating-point arithmetic, which can cause minor precision errors during aggregation. Power Query and DAX may display extra digits due to these rounding artifacts. To ensure consistent precision, consider using Number.Round in Power Query or ROUND in DAX to limit decimals explicitly.