How to add a Grand Total and Grand Average to a basic PowerPivot

[tbobolz]

Excel 2010, V2.

Hi All,

I have a basic power pivot table with years in the rows and months in the columns. So across the top it displays a column for Jan to Dec with a Grand Total in the last Column; however, I would like to also add a column that displays the average of all the months displayed. Yet I also need it to average only the number of current months. So a calulated field would not work if it was set to divide by twleve. I need it to adjust just like the grand total does as the file is updated monthly from one month to twelve months.

Thanks for any suggestions you can offer

Terry

 

[scottsen]

Oh. better answer on the 2 grand totals rows...
Grand Total Mania! (Totals at Top & Multiple Totals) « PowerPivotPro

 

[tbobolz]

Ahhhh, I think I am almost there, here is what I did to copy your suggestion:

=sum('EPSI SOAR MNDOG'[1-CASES])

Already had and works fine


=CALCULATE(AVERAGEX('EPSI SOAR MNDOG'[CASES]), VALUES('EPSI SOAR MNDOG'[DISCHARGE - FISCAL YEAR]), VALUES('EPSI SOAR MNDOG'[DISCHARGE - FISCAL MONTH DESC]))

Gives me the following error: Too few arguments were passed to the AVERAGEX function. The minimum argument count for the function is 2.

 

[scottsen]

If you are just passing a column, use AVERAGE(). AVERAGEX() is for doing stuff that is more complicated than that.