# Grand total average for sum sales per month

This is a discussion on Grand total average for sum sales per month within the PowerPivot Questions forums, part of the Question Forums category; I have a sales fact table and created a pivot table like this: Month Value Jan 200.00 Feb 300.00 Mar ...

1. ## Grand total average for sum sales per month

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?

2. ## Re: Grand total average for sum sales per month

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.

3. ## Re: Grand total average for sum sales per month

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?

4. ## Re: Grand total average for sum sales per month

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])`

5. ## Re: Grand total average for sum sales per month

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...-up-correctly/

6. ## Re: Grand total average for sum sales per month

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

7. ## Re: Grand total average for sum sales per month

Originally Posted by 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])`

This solved it for me! Thanks!

8. ## Re: Grand total average for sum sales per month

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.

9. ## Re: Grand total average for sum sales per month

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.

10. ## Re: Grand total average for sum sales per month

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.

Originally Posted by 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•