Pivot Table - Summing Averages in Grand Total

Copper Contributor

I have a column of values in a pivot table summarized as averages. I want the grand total for this column to add the averages as a sum. I know I can manually add the values with a simple formula. But is there a way to adjust the field settings to do the aforementioned?

4 Replies


Not as far as I know.

Keep in mind that a sum of averages is meaningless.


If add data to data model you may play with DAX measures. For such model


it could be

average with sum:=SUMX (
        "averages", AVERAGE ( Table1[V] )


averages:=AVERAGE( Table1[V] )
average with sum 2 :=
    VALUES ( Table1[A] ),

@HansVogelaar A sum of averages is not necessarily meaningless. Consider a pivot table that consists of nutrition data (such as calories) per meal. Meals (lunch, dinner) are the rows, and calories are the column. I would want to see my average calories per meal, but, for the Grand Total, I would want to see the sum of the averages to know what my average calories per day (across all meals) is.


OK, in that setup you can use it, since you have only one of each meal per day (unless you're a hobbit).