Forum Discussion
Pivot Table needs Grand Total of non-calculated field
Excel on a Mac.
I have a spreadsheet of customers with multiple payments (checks) and each payment (check) is paying multiple invoices. I only have the total amount of each check, but a list of the invoices that check is paying.
To prevent the pivot table from totalling all the invoices listed for a single check, I had to use Max of the value of the check. Otherwise, if there were (for example) 5 invoices paid on a single check, I got the total amount of the check times 5.
But now my Grand Total is also only using Max of the value of the checks which is only giving me the amount of the largest check in the table. How do I get my Grand Total to actually add up the values of all the individual checks?
I hope that makes sense.
TIA
Subtotals and Grand Total uses calculated filed formula as well, i.e. MAX() in your case. Since Excel on a Mac doesn't support data model, the workaround could be to calculate Grand Total outside the PivotTable, see for example
excel - Correct Grand Total in Pivot Using Calculated Field - Stack Overflow
How to Add and Use an Excel Pivot Table Calculated Field (trumpexcel.com)
