# Grand total average for sum sales per month



## miguellagos

I have a sales fact table and created a pivot table like this:

Month              Value

Jan                  200.00
Feb                  300.00
Mar                  500.00

Grand Total     1,000.00

I want to change only the Grand Total to Average function:

Month              Value

Jan                  200.00
Feb                  300.00
Mar                  500.00

Grand Average    333.33

Is this possible?


----------



## XL&ME

Go to Field Settings on the Ribbon, or click on the values field in the Pivot Table Field List under the values field, click on the dropdown arrow and select value field settings.  Then select Average from the box in the middle.


----------



## miguellagos

This is not the solution I want, because it changes the monthly sum sales to monthly average sales.
I need sums per month and average in the grand total.

I have an idea: two pivot tables controlled by the same slicers.

The first pivot table with the sum sales per month and the grand total hidden.

The second pivot table without the detail of months using average and the grand total hidden. The second pivot table working as the grand total of the first pivot table. I'm going to try this idea tomorrow.

Anyone with an easier solution?


----------



## ruve1k

First create a measure ValueSum:=SUM(Table1[Value])
Then create a measures that takes the average of the monthly totals of the previous measure.


		Code:
__


=AVERAGEX(VALUES(Table1[Month]),[ValueSum])


----------



## powerpivotpro

Full blog post on making totals and grand totals add up differently than their individual row or column values:

http://www.powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/


----------



## miguellagos

At the end my solution is the grand average outside of the pivot table ...


----------



## JeffCat

ruve1k said:


> First create a measure ValueSum:=SUM(Table1[Value])
> Then create a measures that takes the average of the monthly totals of the previous measure.
> 
> 
> Code:
> __
> 
> 
> =AVERAGEX(VALUES(Table1[Month]),[ValueSum])




This solved it for me!  Thanks!


----------



## peteyian1

The way i solved this issue:

Right click Pivot Table
Select "Pivot Table Options"
Go to "Totals and Filters" tab
Deselect "Show grand totals for columns"

Then simply do an average function below the table (not in the table) and select the data you want. You can also select additional cells so that as your database grows (and your pivot table with it), you can simply refresh your pivot table and the averages will update automatically.


----------



## ruve1k

peteyian1,

Your method will return an average of all values in the underlying data. 
The original poster requested an average of the monthly totals. 
These are two different calculations.


----------



## peteyian1

No, you aren't changing the functions within the pivot table, so it still returns rows that sum sales per month from the database. You write the average function outside the pivot table so that it simply references the pivot table data. 



ruve1k said:


> peteyian1,
> 
> Your method will return an average of all values in the underlying data.
> The original poster requested an average of the monthly totals.
> These are two different calculations.


----------

