Mar 24 2021 10:06 PM
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....
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.