Pivot table Values section

New Contributor

Hi all,

I summarize my values as average, thus the grand total shows as average too.

Is it possible to change grand total into sum(a total of the value column) while maintaining the individual value above as average?

Thanks!

2 Replies

@KellyKang 

 

You cannot do this with Normal Pivot Table but if you import your data into Power Pivot Data Model and then you may create some Measures and insert the Pivot Table using the data in Data Model to achieve this.

 

In the attached, I converted the Data into an Excel Table and then sent it to the Power Pivot Data Model and created the following Measures....

Total Sales:=SUM(Data[Total])
Average Sales:=AVERAGE(Data[Total])
IsFilteredOffice:=ISFILTERED(Data[Office])
Average & Total Sales:=IF([IsFilteredOffice],[Average Sales],[Total Sales])

 

Now insert a Pivot Table from Power Pivot Data Model and put Office in Rows and the Total Sales, Average Sales and Average & Total Sales Measures in the Values and you will get a Pivot Table like below...

Power Pivot.jpg

 

Please refer to the attached for more details.

 

 

@Subodh_Tiwari_sktneer 

 

thank you Subodh!

I followed your suggestions but got the same average number as attached..

Do you know what is the problem?

regards,

Kelly