Pivot Table - Calc Field

[porkchops2]

I am struggling to figure out how to generate a calculated field within my pivot table. Below is an example of my pivot table and the Discrepancy column is what I need to generate through a formula. I've also included a copy of my source data. I need to know when the total of the line discounts for an invoice do not match with the discount amount for an invoice.

 

[JB2020]

Select 'Fields Items & Sets' > 'Calculated Field...' from the pivot table menu, then enter the formula as 'Line Discount' - Discount.

 

[porkchops2]

Unfortunately, that formula appears to be summing the Discount column and gives an invalid sum total if the invoice has more than one line.

 

[JB2020]

Unfortunately, that formula appears to be summing the Discount column and gives an invalid sum total if the invoice has more than one line.

View attachment 26599

Will the 'Discount' be the same for each row? If so, you could add a helper column containing the Discount/The number of rows for that invoice and use that in your calculated column.

=Discount/COUNTIF(Invoices,Invoice)

Therefore the sum of all of these should add up to the original figure.