英文:
Inconsistent results from logical formula in PowerPivot
问题
I need some help troubleshooting a formula in PowerPivot. I am comparing the invoice total on a summary table to the sum of the line amounts on a detail table. The formula sometimes returns that the fields don't match when they do in fact match.
Data Model
An external/OLAP data model consisting of two tables InvSummary
and InvDetail
. These tables are linked by InvoiceNumber
.
Calculated fields
Total Revenue
defined onInvSummary
as=SUM(InvSummary[Total Invoice Amt])
Total Line Amt
defined onInvDetail
as=SUM(InvDetail[Line Amt])
InvAmtMatch
defined onInvSummary
as=[Total Revenue]=[Total Line Amt]
Sample data
In the images below, the fields have exactly the digits shown, no extra decimal places that are rounded.
Pivot table
The first 4 columns are the pivot table. The fifth column is a manual check on equality of Revenue and Line Amt using relative cell references.
Invoice Summary
Invoice Detail
英文:
I need some help troubleshooting a formula in PowerPivot. I am comparing the invoice total on a summary table to the sum of the line amounts on a detail table. The formula sometimes returns that the fields don't match when they do in fact match.
Thanks for taking time to read my question!
Data Model
An external/OLAP data model consisting of two tables InvSummary
and InvDetail
. These tables are linked by InvoiceNumber
.
Calculated fields
Total Revenue
defined onInvSummary
as=SUM(InvSummary[Total
Invoice Amt])Total Line Amt
defined onInvDetail
as
=SUM(InvDetail[Line Amt])
InvAmtMatch
defined onInvSummary
as=[Total Revenue]=[Total Line Amt]
Sample data
In the images below, the fields have exactly the digits shown, no extra decimal places that are rounded.
Pivot table
The first 4 columns are the pivot table. The fifth column is a manual check on equality of Revenue and Line Amt using relative cell references.
Invoice Summary
Invoice Detail
答案1
得分: 1
这似乎是一个精度问题,与 Power Query 和 Excel 如何处理浮点数有关。
在 PQ 中观察到以下情况:
let
Source = 0.1+0.2=0.3
in
Source
结果:
在 Excel 中:
结果:
英文:
Fairly sure this is a precision problem and due to how power query and excel manage floating point numbers.
Observe the following in PQ:
let
Source = 0.1+0.2=0.3
in
Source
Result:
In Excel:
Result:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论