Justin Baity on 22 Mar 2023 14:12:28
When summing dollar values, the result sometimes yields a different value on the order of +/- $0.00000000000001. This is a problem in accounting (or, more accurately, gaining approval for PBI from accountants). After review of documentation this appears to be an inherent issue in all modern computers built on the IEEE 754 standard, and is replicable in Excel....however, Excel seems to mask it much better. The only 100% consistent fix I've found in Power BI is to use a DAX measure with ROUND( SUM(....),2).
In Power Query a summation can be forced to use fixed decimals with the Precision.Decimal argument in List.Sum, but then the issue crops up again when the front end sums up for a visual where no such option exists. Setting the column type in the modeling tab to dollar value with 2 decimals still lets this issue through on view-as-table and extracts.