So, while in the “Summarise values fields by” section, one may want to use the Maximum function, in the subtotal cell, one may want to use the sum function. The Subtotal/Grand Total cells assume the same function as has been used in the “Summarise Values field by”. While this is easy to accomplish with a Conventional Pivot Table as well, the problem occurs in the Subtotal/Grand Total cells of a Pivot Table. One may want to know the maximum amount per month per Manager. There are repetitions in the Data and Manager column.
Assume a dataset with three columns – Date, Manager and Amount. You may refer to my solution in this workbook.
#EXCEL PIVOT TABLE ADD FIELD TO GRANDTOTAL FREE#
I have been able to resolve this problem by using the free Power Pivot add-in from Microsoft for Excel 2010 and higher versions. The Grand Total column is to be left blank for Average Selling Price,Īs you can observe, the subtotal column (for the months) will have different formulas running for different Fields.Ī conventional Pivot Table does not allow one to have custom formulas in the Subtotal columns. For the monthly subtotals, the figure is to be computed as Average Selling price for every year is to be computed as as Net Amount/(Ttl Bonus + Quantity). The Grand total column should be a a summation of individual subtotals.Ģ. For the monthly subtotals, the Net Amount and Bonus figure are to computed as a difference of 20 i.e. SUM of quantity of 2005 – SUM of quantity of 2006. The actual requirement is to customise the Subtotal column of the Pivot Table as follows:ġ. There is no complication in creating the Pivot Table described above. and the summarization function applied to them is SUM. In the Value area section are fields such as Net Amount, Quantity, Bonus etc. For some months, there is data for two years only 20. In the Column labels are two fields, Month and then Year – so in the column labels, for every month, there is data for three years 2005, 20. However, the other 2 are much larger and an actual solution to the problem would be a better route to go.Visualise a Pivot Table with a few Fields dragged in the Report filter, Row labels and Value Area section. It should be noted, rebuilding one of smaller spreadsheets DID work. There are random threads going back more than a decade, but no one seems to have an actual fix (other than the one above, and others saying to rebuild the spreadsheet). To note, there is a "supposed" fix on some other threads, always the same, but does not work for me:ġ) Right click your pivot table -> Pivot table options -> Data -> Change "Number of items to retain per field"Ģ) Wipe all rows in your data source except for the headersĤ) Save, and close all instances of ExcelĪgain, this "fix" did not work (at least for me).Īny help with this issue would be greatly appreciated. The files are upwards of 20-30MBs each, some with upwards of 400 pivot tables and other references.So not quick to rebuild I hadn't seen this until about a year ago when one spreadsheet suddenly started doing this, then another, and now I have 3 of them. However, my a few of my spreadsheets show the following (note the "2" after DEF): Mass of confidential data that I cannot upload):Ĭonsidering the above table, a pivot should display a summary as follows: A simple example without loading the spreadsheets (they contain a This does NOT pertain to a second header with the same name, but a Field.
I am having the same issue, and it is currently happening multiple workbooks (each having their own data sources).