Grand total average for sum sales per month

miguellagos

New Member
Joined
Apr 1, 2012
Messages
8
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?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
 
Upvote 0
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?
 
Upvote 0
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])
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top