Average of total per month in pivot table

[kcmuppet]

If I produce a pivot table that takes data with a date record and produces a sum per month, how can I then add to the pivot table a column that calculates the average per month (as the average of each monthly total).

I realise I can simply average the cells outside of the pivot like in the example below:

But how can I create a calculated column?
When I try in a regular pivot table, all I can seem to do is generate a monthly average in each column (not what I want),

Do I need PowerPivot and a DAX measure?

 

[estephenkim]

Are you trying to do something like what's in the image? If so, then try the following:

Instructions: Add an average calculated field to your PivotTable.

1. Click any cell in your PivotTable.
2. In PivotTable Analyze tab, select Fields, Items, & Sets in the Calculations group.
3. Select Calculated Field.
4. In the Name field of the Insert Calculated Field dialog box, give your calculated field a name (e.g., Average).
5. Copy and add the following formula in the Formula field: =AVERAGE(Sep,Oct,Nov,Dec,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep2)
6. Click Add, then OK. This adds your new Average calculated field to your PivotTable Fields list.
7. Drag and drop the Average field to the PivotTable ∑ Values area. Rename column header and format values as desired.

 

[kcmuppet]

Are you trying to do something like what's in the image?

@estephenkim Thanks for your reply

I am trying to do something like that, except that the columns for each month are derived from a date field - i.e. the Columns part of the PivotTable Fields has
Date(Year)
Date (Month), like this:

That means a calculated field, e.g. =Average(... can't use individual month names

 

[estephenkim]

I see...

When you enter the formula for your average calculated field, you should see a list of available Fields from which you can choose (see highlights in image). After the opening parenthesis, you should be able to click each field to add it to your formula (the field names should correspond to the column headers in your underlying data table upon which the PivotTable is based). Be sure to add commas between each field and the closed parenthesis before clicking Add and OK. Does this work in your case?

 

[kcmuppet]

.... After the opening parenthesis, you should be able to click each field to add it to your formula (the field names should correspond to the column headers .

The problem is you can't average a summary date field