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

@Bjsmith2 

Not as far as I know.

Keep in mind that a sum of averages is meaningless.

@Bjsmith2 

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

image.png

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]
)

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

@kptaylor 

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