I have data which I pivot as Row Labels
and Column Labels
which gives me something like this:
And what I would like to do is add columns like Grand Total
(i.e. which applies to all columns as a whole, and not each column individually) to calculate things such as Average
, Min
, Max
, Standard Deviation
…..
I have found a way to change the Grand Total
column (more options gives you more calculations like standard dev):
However I can't find a way to add more columns (i.e. I would like to keep Grand Total
, and add more columns for Average
, Min
, Max
, Standard Deviation
How can I add a calculated column to my pivot table in a similar way to Grand Total?
UPDATE: Adding a calculated field to my pivot table
However whatever I do, my calculated field shows the same as the SUM
.
Am I doing something wrong with the calculated field?
Best Answer
What you need is not a calculated field, but a calculate item.
Your column field has 12 items, namely the 12 months of a year. What you want is an additional column called
Avg
. So we need to addAvg
besides the 12 items:Activate (click on) any cell with a label of the column field, say the cell with
Jan
, then PivotTable tools -> Options -> Fields, Items & Sets -> Calculated Item (it is dimmed if you do not activate a cell for field labels.) Then, name a new itemAvg
and set the formula=AVERAGE(Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
Add other calculated items similarly.