Search code examples
excelpowerquerypowerpivot

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.

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.

enter image description here

Invoice Summary

enter image description here

Invoice Detail

enter image description here

Solution

  • 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:

    enter image description here

    In Excel:

    enter image description here

    Result:

    enter image description here