Search code examples
excelpivotpivot-tablepowerpivot

Excel Pivot table Grand Average instead of Grand Total for sum of head count by month

i'm sure someone figured a workaround for this as there seems to lots of people with the same question...

i need a pivot table that calculated total headcount sum each month, but need to have a column for average headcount between the months selected (on the pivot table)

in other words, i will have a timeline slicer tied to the pivot table and want to have the average for the range of months selected for the pivot.

i found a thread mentioning using power pivot and adding a column of total count, but havent found the solution i'm looking for...

as shown in the snip of pivot table below, i need the "Grand Total" to show average (or a way to have average in the pivot table...

eg count in sept of 56 and count in Oct of 59 should have a Grand Average of 57.5... instead of the total of 115...

enter image description here

EDIT 1:

i like where this is going but can't seem to get it working. i have the following written and my measure:

MyMeasure := VAR T1 = SUMMARIZE(HC_Data, [data date (Month)], "Total Value", SUM(HC_Data[HC count]) RETURN IF( HASONEVALUE(HC_Data [data date (Month)]), SUM( HC_Data[HC count]), AVERAGEX( T1, [Total Value] ) )

and get a DAX sintax error

Solution

  • Try the following measure in Power Pivot:

    MyMeasure :=
VAR T1 =
    SUMMARIZE( Table1, [Date (Month)], "Total Value", SUM( Table1[Value] ) )
RETURN
    IF(
        HASONEVALUE( Table1[Date (Month)] ),
        SUM( Table1[Value] ),
        AVERAGEX( T1, [Total Value] )
    )