Calculated Field in a pivot table using a Pivot Table Grand Total

[kcstam]

Need help please with a Calculated Field in a pivot table using a Pivot Table Grand Total in the formular.

Example:
Acme Corp purchased 3 items from ABC Co and received three invoices (A01, A02, A03) form ABC Co for those items.

Below is a link to dropbox where I have the pivot table and original data source. The pivot table shows how much was paid on each invoice and a Grand Total of ‘Amount Paid’ for each invoice.

Problem:
Please see Column I, “Balance Owed” (Col C – Col H), which shows the balance of how much Acme owes ABC Co for each invoice, with a Total amount owed at the bottom of the Col I. This is not part of the pivot table, but I would like it to be. Does anyone know how I could make Col I a part of the pivot table? Thanks for any help anyone may be able to provide!

NOTE</SPAN>: I tried adding a Calculate Field that subtracts ‘Invoice Amount’ from ‘Amount Paid’ but this puts the calculation to the right of each ‘Payment Date’ / ‘Payment Number’ column. Instead, I want one column that represents the ‘Invoice Amount’ less the Grand Total of ‘Amount Paid’, i.e., Balance Owed, as represented in Col I.


https://www.dropbox.com/l/9W7aDfmBOiabRy4AbNUVPr<o></o>
</SPAN>

 

[par60056]

Welcome to the forum.

I'm sorry to say but I don't think there is a way that you can do what you want with the pivot table.

What you can do is write a sheet change event procedure. I don't think this is a good thing but it is a way that you can update the information.

What if their next payment is $120? How do you enter that into the source sheet? If you are stuck with this format for the source data, it will be a difficult (not impossible) task. I would tend to rethink the source data layout, if that is an option, to better solve the problem.

 

[kcstam]

Need help please with a Calculated Field in a pivot table using a Pivot Table Grand Total in the formular.

Example:
Acme Corp purchased 3 items from ABC Co and received three invoices (A01, A02, A03) form ABC Co for those items.

Below is a link to dropbox where I have the pivot table and original data source. The pivot table shows how much was paid on each invoice and a Grand Total of ‘Amount Paid’ for each invoice.

Problem:
Please see Column I, “Balance Owed” (Col C – Col H), which shows the balance of how much Acme owes ABC Co for each invoice, with a Total amount owed at the bottom of the Col I. This is not part of the pivot table, but I would like it to be. Does anyone know how I could make Col I a part of the pivot table? Thanks for any help anyone may be able to provide!

NOTE</SPAN>: I tried adding a Calculate Field that subtracts ‘Invoice Amount’ from ‘Amount Paid’ but this puts the calculation to the right of each ‘Payment Date’ / ‘Payment Number’ column. Instead, I want one column that represents the ‘Invoice Amount’ less the Grand Total of ‘Amount Paid’, i.e., Balance Owed, as represented in Col I.


https://www.dropbox.com/l/9W7aDfmBOiabRy4AbNUVPr<o></o>
</SPAN>

Thank you for the reply, I was afraid that would be the answer. I'll try and rethink the data source layout.