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



## tbobolz (Jul 9, 2014)

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 (Jul 9, 2014)

I might be missing something.

If you wrote your own average, then ya... you would have to be careful how you divide (some sort of COUNTROWS()).  But if you just use 
   =AVERAGE(MyTable[MyColumn]) or AVERAGEX(MyTable, [MyExpression]) 
won't the right thing magically happen?


----------



## tbobolz (Jul 17, 2014)

Thanks for the help Scott! This is what I originally did "=AVERAGE('EPSI SOAR MNDOG'[1-CASES])"; however, it inserts a average column for each month in the colums and well as the grand total of months. Plus it returns the value of "1" for everything. SO, to recap I have a table that sums Cases by month in eacd column, then provide a grand total. I wish to add a average of the months only once next to the grand total. So as the year progresses from 1 to 12 months, within the pivot is updates accordiningly. 

I meant an average of months next the the subtotal for each year(s) not Grand totals.

Sorry

Thanks again.
Terry


----------



## scottsen (Jul 17, 2014)

Think I am gonna need a screen shot or sample workbook.

On the Pivot Table Options, you can turn off sub-totals rows/columns, does that help you?

Though... returning an average of '1' all over certainly sounds... odd.


----------



## tbobolz (Jul 17, 2014)

Active Pivot TableSum of CaseColumn Labels20132013 Total20142014 TotalRow LabelsJanFebMarAprMayJunJulAugSetOctNovDecJanFebMarAprMayJunJulAugSetOctNovDecA424242424242362224244242230B224244242223024242424242436C4242424242423624242424242436D424424222223024442222224Grand Total148166166168148128132814616616614812812126

<COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY>

</TBODY>


If I add in the new measure that is written like this:    =average([Case])

I get this pivot:


Column Labels20132013 Sum of Case2013 Monthly Average20142014 Sum of Case2014 Monthly AverageJanFebMarAprMayJunJulAugSetOctNovDecJanFebMarAprMayJunJulAugSetOctNovDecRow LabelsSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageSum of CaseMonthly AverageA4121412141214121412141213612121214121414121412121301B2121412141412141212121301214121412141214121412141361C412141214121412141214121361214121412141214121412141361D4121414121412121212121301214141412121212121241Grand Total14181161611616116181141811218113218114161161611616114181121811211261

<COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><TBODY>

</TBODY>
I perfer it to look like this and have it auto update as months are added into the raw data.


*Sum of Case**Column Labels*                            *2013*           *2013 Monthly Average**2013 Total**2014*          *2014 Monthly Average**2014 Total**Row Labels**Jan**Feb**Mar**Apr**May**Jun**Jul**Aug**Set**Oct**Nov**Dec*  *Jan**Feb**Mar**Apr**May**Jun**Jul**Aug**Set**Oct**Nov**Dec*  A42424242424233622242442422330B22424424222330242424242424336C424242424242336242424242424336D42442422222330244422222324*Grand Total**14**8**16**6**16**6**16**8**14**8**12**8**11**132**8**14**6**16**6**16**6**14**8**12**8**12**11**126*

<COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><TBODY>

</TBODY>

Thanks again for your help!


----------



## scottsen (Jul 17, 2014)

On the columns, where you have year and month, do you also see a cute "sigma" symbol?  Does dragging that ****** up or down magically fix your problem?


----------



## tbobolz (Jul 18, 2014)

Yes I pulled the Sigma up and it shifts the average columns all to the right; however, I only want a column for the subtotal average and not the individual months, plus it is still returning all "1". This seems like I am missing something simple. 

Again, thanks for the help here's the results. I would upload the work book but I do not know how that is done on Mrexcel.


Active Pivot TableColumn LabelsSum of CaseMonthly Average20132013 Total20142014 Total20132013 Total20142014 TotalRow LabelsJanFebMarAprMayJunJulAugSetOctNovDecJanFebMarAprMayJunJulAugSetOctNovDecJanFebMarAprMayJunJulAugSetOctNovDecJanFebMarAprMayJunJulAugSetOctNovDecA4242424242423622242442422301111111111111111111111111B2242442422230242424242424361111111111111111111111111C424242424242362424242424243611111111111111111111111111D4244242222230244422222241111111111111111111111Grand Total14816616616814812813281461661661481281212611111111111111111111111111

<COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY>

</TBODY>


----------



## scottsen (Jul 18, 2014)

Not sure what you mean about the "1" (I dont see that in your paste), but ya... let's get the workbook uploaded.   Just use dropbox, google drive, microsoft onedrive, or similiar.


----------



## tbobolz (Jul 18, 2014)

scottsen said:


> Not sure what you mean about the "1" (I dont see that in your paste), but ya... let's get the workbook uploaded. Just use dropbox, google drive, microsoft onedrive, or similiar.



Ok here is the link to the file:  https://drive.google.com/file/d/0B3g_N9q156QuM2ZBV25wazNIR2s/edit?usp=sharing

Not sure why mrexcel site is not displaying it to you, I am able to view and scroll right to see it all.'

Thanks again.


----------



## scottsen (Jul 18, 2014)

Oh, I could see the 1's... I was just being dumb 

Anywaay, you wrote  =AVERAGE(Table1[Case]) ... and since every case had a value of 1... the average was... 1 

[Total Cases] := SUM(Table1[Case])
[Monthly Avg Case] := Measure 1:=CALCULATE(AVERAGEX(Table1, [Total Cases]), VALUES(Table1[Year]), VALUES(Table1[Month]))

Calculate the average... across each unique Year/Month.

To get 2 total rows, without have double value rows... you are going to have to use the weird "Manage Sets" option I believe to delete all the value rows you don't care about.  On the PivotTable | Options tab, under Fields, Items and Sets.


----------



## tbobolz (Jul 9, 2014)

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 (Jul 18, 2014)

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


----------



## tbobolz (Jul 21, 2014)

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 (Jul 21, 2014)

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


----------

