Pivot table average

cchildaa 

Let say we have such sample table, calculate both Average and Average of Average.

Creating PivotTable add data to data model

In Excel options keep disabled automatic grouping

It's always better to have separate Date (aka Calendar) table, which could be created in Power Pivot or by other way, but we could skip that. When you add dates to PivotTable Rows, Excel automatically creates additional columns like Months

Now create in Power Pivot two measures

Value Average:=AVERAGE ( Table1[Value] )

which actually repeats implicit measure when we aggregate values, and

AoA:=IF (
    HASONEVALUE ( Table1[Date (Month)] ),
    [Value Average],
    AVERAGEX ( VALUES ( Table1[Date (Month)] ), [Value Average] )
)

This one works as Average within the month (i.e. it has one value), and averages first measure iterating all months for the totals.

First measure returns average of all values for all months

and another one average of averages