![how to make item grandtotal in pivot table how to make item grandtotal in pivot table](https://support.content.office.net/en-us/media/6f64c757-f938-4f97-a9c2-a42ac8fcd83e.png)
The pivot table will then contain everything calculated both ways, so everything you need is there. Add another field that's the SUM of the new Rounded data column, as in my example. In the pivot table, keep your calculated rounding field since that displays the aggregations in the form you need. This is easy to maintain as you add or modify client records. The best way to accomplish what you need may be combining calculations internal and external to the pivot table.Īdd the Rounded calculation, like I did, as another data column that feeds the pivot table.
![how to make item grandtotal in pivot table how to make item grandtotal in pivot table](https://excelinexcel.in/ms-excel/wp-content/uploads/sites/2/2020/08/1-23.png)
But that won't necessarily break everything every time the data changes (although you will need to trigger recalculation of the pivot table).įrom the discussion, you have some complex requirements that benefit from the aggregation the pivot table offers, but don't lend themselves to doing it in a simple, straightforward way.
![how to make item grandtotal in pivot table how to make item grandtotal in pivot table](https://www.techonthenet.com/excel/pivottbls/images/percentage_grand2007_001.gif)
If what you require is the aggregation shown in your example (aggregate, then round), you can't use a pivot table to spit out a total based on calculating it a different way. There are limitations to what you can do in a pivot table. To preserve some of the discussion in the comment thread: For pivot tables with any complexity, it's a good practice to double check the results to confirm that it's doing what you want. aggregating, especially when you apply calculated fields or even use aggregation functions that involve more complex calculations than simple summing or counting. Pivot tables are a fantastic tool for simple aggregations, but you need to be careful about the sequence in which Excel applies calculations vs. The column label in your pivot table would more accurately be "Rounded of Sum". So despite the column header label, the pivot table isn't performing the calculation you expect. What is happening in your case is that Excel is aggregating the raw income values from your data table, then applying the rounding calculation to the aggregate. If you need a calculation applied to each raw value prior to aggregating, do it outside of the pivot table. You can see that the pivot table correctly summarizes the data when you do it this way (which is the solution to getting the result you want). I had to create another column with the rounded values, and then include that column in the pivot table: I don't have access to a version of Excel that features inserting calculated fields, but I can demonstrate what is happening in your case (which you correctly identified in the question).