In this screenshot, we see two projects. The last column is generated using a TODATE function. The first column is calculated as the sum of columns b-through-f. The body of each project shows the correct data. The subtotal row for each project shows the correct data. For each project, we want the subtotal row to show the opening balance from the first month, and the ending balance from the last month. We have set the appropriate aggregation options for the pivot table, and we are seeing what we want within each project and on the project subtotals.
The problem is the Grand Total row, which uses those same first and last aggregation methods. The aggregation method required for each project is not the aggregation method required for the grand totals.
Our Grand Total opening balance should be $5.085mm, the sum of the opening balances for the two projects. Our Grand Total ending balance should be $3.458mm, the sum of the ending balances for the two projects. How can this be accomplished?
