Pivot Table repeating data

[churrtt]

I created a report that pulls raw data from QuickBooks that shows the payments we've received from customers over a certain time frame and gives the detail on the payment itself as well as the invoice the payment was applied to. When we receive separate payments on different days that are applied to the same invoice, the invoice detail gets repeated in the pivot table due to the two different payments. This causes a error in the total amounts because the amounts are also duplicated. How do I change my PivotTable around to stop the duplication but still show all detail? Screenshot below for reference, the duplicated data is highlighted.

 

[johnny51981]

Do you, by chance, track the payments made at the Item Line level?

If not, then I would suggest using Max of Payment Date and then Sum of Payment Amount.

 

[churrtt]

Do you, by chance, track the payments made at the Item Line level?

If not, then I would suggest using Max of Payment Date and then Sum of Payment Amount.

I do track payments made at the item line level, how would I use that information to solve the issue?

 

[johnny51981]

Change your Invoiced Line Item Quantities and Amounts to Payment Receive Quantities and Amounts. You can then leave the Payment Dates and Payment Amounts in your Pivot without there being any duplication.