I have a simple reporting template for outstanding debtors that uses a pivot table to calculate each debtor's balance whenever I update the Invoices tab. The pivot table is grouped by date as Months & Years. Attached is a sample workbook showing what is not working (yellow highlight) and how I would like it work (blue highlight).
My boss wants to see a percentage that each month represents against the grand total. I know how to add a calculated field by row, but cannot seem to do it by column.
I have worked around the issue by adding formulas in row 4 but each month when I update the data and refresh the pivot table some of the percentage formulas report #REF! and I have to manually set them up again.
Could someone advise me whether this can be set up so that the percentages will automatically update please?
Thanks for taking the time!!!
Bookmarks