Jun 17 2022 07:13 AM
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?
Jun 17 2022 07:54 AM
Jun 17 2022 02:06 PM
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]
)
Feb 12 2024 01:26 PM
@Hans Vogelaar 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.
Feb 12 2024 02:09 PM
OK, in that setup you can use it, since you have only one of each meal per day (unless you're a hobbit).