On a pivot table, I want the grand total to be the average. When I code it, the code changes all the values in the column to an average.

if a person's % for April is 95% & the sum of all the people in the table is 1924%. If I change the grand total to average, the person's april % changes to 19% (which is an average instead of a total.



'=== find row with Grand Total
endrow = 1
Do While Left(Worksheets("PivotTable_Resource").Cells(x, 1), 5) <> "Grand"
    endrow = endrow + 1
Loop

'== modify grand total from sum of % to average %
Worksheets("PivotTable_Resource").Range("N45").Select
    ActiveSheet.PivotTables("PivotTable_Resource").PivotFields(" Apr 14 - 160 (%)") _
        .Function = xlAverage