Does PowerBI apply rounding when applying aggregations (SUM)?

[powerbidu]

Hi all

I have the below sample data set. 

Identifier	Value
A	0.05
A	0.2925
A	0.1575
A	0.065
A	0.034
A	0.065
A	0.136
A	0.1
A	0.1
B	0.034
B	0.2925
B	0.136
B	0.1
B	0.1575
B	0.1
B	0.065
B	0.05
B	0.065

I plotted a table with two columns -  'Identifier' and 'Sum' and am interested to flag out exceptions. Exceptions are identifiers whose total sum of all rows does not equal to 1.

In the above 2 instances, the sum of all rows with Identifiers A / B add up to 1 in Excel. However it flags as an exception when applied in PowerBi. Can anyone explain to me why that might be the case (I am thinking it could be a rounding issue in Powerbi) and whether there are any ways to resolve this? I do not want Identifiers A / B to be flagged as exceptions.

Thanks in advance.

[OwenAuger · Accepted solution]

Hi @powerbidu 

Column values of type "Decimal number" are inherently approximate,  and comparing two Decimal number values for equality may give unexpected results (see documentation here).

Potential solutions:

1. Change the Value column to type "Fixed decimal number", which can represent values with at most four decimal places, and is internally stored as an integer value divided by 10^4.

2. Leave Value's type as "Decimal number" but create a flag with tolerance built-in.

For example:

Flag = 
VAR Tolerance = 1E-8
VAR ResultBool =
    ABS ( SUM ( Data[Value] ) - 1 ) > Tolerance
VAR ResultInt =
    INT ( ResultBool )
RETURN
    ResultInt

Then add a visual-level filter of Flag = 1:

Having said all that, I couldn't replicate your issue with the sample data your posted. Both appeared to sum to exactly 1 for both Identifiers A & B with a column of Decimal number type. However, in general, it's best not to rely on decimal values summing exactly to a particular value.

Do any of the above work for you?