Forum Discussion
Does PowerBI apply rounding when applying aggregations (SUM)?
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.
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 ResultIntThen 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?
2 Replies
- OwenAuger
Super User
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 ResultIntThen 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?
