# Pivot Table - Summing Averages in Grand Total

Copper Contributor

# Pivot Table - Summing Averages in Grand Total

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

# Re: Pivot Table - Summing Averages in Grand Total

Not as far as I know.

Keep in mind that a sum of averages is meaningless.

# Re: Pivot Table - Summing Averages in Grand Total

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

it could be

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

or

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

# Re: Pivot Table - Summing Averages in Grand Total

@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.

# Re: Pivot Table - Summing Averages in Grand Total

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