Mar 24 2021 10:06 PM
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!
Mar 24 2021 11:08 PM
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...
Please refer to the attached for more details.
Mar 25 2021 03:22 PM
thank you Subodh!
I followed your suggestions but got the same average number as attached..
Do you know what is the problem?
regards,
Kelly