在PowerPivot中的逻辑公式产生的结果不一致。

huangapple go评论71阅读模式
英文:

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 on InvSummary as =SUM(InvSummary[Total Invoice Amt])
  • Total Line Amt defined on InvDetail as =SUM(InvDetail[Line Amt])
  • InvAmtMatch defined on InvSummary 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.

在PowerPivot中的逻辑公式产生的结果不一致。

Invoice Summary

在PowerPivot中的逻辑公式产生的结果不一致。

Invoice Detail

在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.

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 on InvSummary as =SUM(InvSummary[Total
    Invoice Amt])
  • Total Line Amt defined on InvDetail as
    =SUM(InvDetail[Line Amt])
  • InvAmtMatch defined on InvSummary 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.

在PowerPivot中的逻辑公式产生的结果不一致。

Invoice Summary

在PowerPivot中的逻辑公式产生的结果不一致。

Invoice Detail

在PowerPivot中的逻辑公式产生的结果不一致。

答案1

得分: 1

这似乎是一个精度问题,与 Power Query 和 Excel 如何处理浮点数有关。

在 PQ 中观察到以下情况:

let
    Source = 0.1+0.2=0.3
in
    Source

结果:

在PowerPivot中的逻辑公式产生的结果不一致。

在 Excel 中:

在PowerPivot中的逻辑公式产生的结果不一致。

结果:

在PowerPivot中的逻辑公式产生的结果不一致。

英文:

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:

在PowerPivot中的逻辑公式产生的结果不一致。

In Excel:

在PowerPivot中的逻辑公式产生的结果不一致。

Result:

在PowerPivot中的逻辑公式产生的结果不一致。

huangapple
  • 本文由 发表于 2023年8月4日 20:55:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/76836119.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定