Home 
Search 
Today's Posts 
#1




Pivot Table 'Grand Total' question, average instead of sum
Hello,
I have a list of data with three columns, these are 'Period', 'Area' and 'Cost'. I want to pivot to show the area's listed down vertically, the period horizontally across and the 'Grand Total' inserted in the pivot table to give an average of the periods instead of the sum. Is this possible? Please advise, much appreciated. Brian Taylor Manchester, England. 
#2




Pivot Table 'Grand Total' question, average instead of sum
if you right click the data in the pivot table and choose field settings you
can change the sum to average. Is this what you want? "Coop Bank" wrote: Hello, I have a list of data with three columns, these are 'Period', 'Area' and 'Cost'. I want to pivot to show the area's listed down vertically, the period horizontally across and the 'Grand Total' inserted in the pivot table to give an average of the periods instead of the sum. Is this possible? Please advise, much appreciated. Brian Taylor Manchester, England. 
#3




Pivot Table 'Grand Total' question, average instead of sum
No, I need the periods to be summed as normal but the end 'Grand Total'
column inserted by the pivot table to show as an average rather than a sum. Regards "Michael" wrote: if you right click the data in the pivot table and choose field settings you can change the sum to average. Is this what you want? "Coop Bank" wrote: Hello, I have a list of data with three columns, these are 'Period', 'Area' and 'Cost'. I want to pivot to show the area's listed down vertically, the period horizontally across and the 'Grand Total' inserted in the pivot table to give an average of the periods instead of the sum. Is this possible? Please advise, much appreciated. Brian Taylor Manchester, England. 
#4




Pivot Table 'Grand Total' question, average instead of sum
Hi Brian
No you can't do that. You can do what Michael says, but add the filed twice, once as sum and once as average, but then you are going to have a very messy table. Right click on the PTTable Optionsuntick Grand Total by Rows. Create a formula to the right of the PT =AVERAGE(B4:N4) or whatever the range is  Regards Roger Govier "Coop Bank" wrote in message ... No, I need the periods to be summed as normal but the end 'Grand Total' column inserted by the pivot table to show as an average rather than a sum. Regards "Michael" wrote: if you right click the data in the pivot table and choose field settings you can change the sum to average. Is this what you want? "Coop Bank" wrote: Hello, I have a list of data with three columns, these are 'Period', 'Area' and 'Cost'. I want to pivot to show the area's listed down vertically, the period horizontally across and the 'Grand Total' inserted in the pivot table to give an average of the periods instead of the sum. Is this possible? Please advise, much appreciated. Brian Taylor Manchester, England. 
#5




Pivot Table 'Grand Total' question, average instead of sum
Thanks thats what I expected, I was just hoping in vain there may be a hidden
dynamic solution "Roger Govier" wrote: Hi Brian No you can't do that. You can do what Michael says, but add the filed twice, once as sum and once as average, but then you are going to have a very messy table. Right click on the PTTable Optionsuntick Grand Total by Rows. Create a formula to the right of the PT =AVERAGE(B4:N4) or whatever the range is  Regards Roger Govier "Coop Bank" wrote in message ... No, I need the periods to be summed as normal but the end 'Grand Total' column inserted by the pivot table to show as an average rather than a sum. Regards "Michael" wrote: if you right click the data in the pivot table and choose field settings you can change the sum to average. Is this what you want? "Coop Bank" wrote: Hello, I have a list of data with three columns, these are 'Period', 'Area' and 'Cost'. I want to pivot to show the area's listed down vertically, the period horizontally across and the 'Grand Total' inserted in the pivot table to give an average of the periods instead of the sum. Is this possible? Please advise, much appreciated. Brian Taylor Manchester, England. 
#6




Quote:
You can do it creating a calculated field in a Pivot Table created with Powerpivot. You need to write the calculation in DAX. However, the correct result depends on how you present the data on the Pivot Table. Best, Paolo 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Pivot Table Grand Total  Excel Discussion (Misc queries)  
Grand Average in Pivot Table?  Excel Discussion (Misc queries)  
Grand total in PivotTable  Excel Worksheet Functions  
Pivot Table  Add column to average Grand Total  Excel Worksheet Functions  
Pivot Table Calculated Field (Grand total question)  Excel Discussion (Misc queries) 