3

I have data like below, with a pivot table, and a calculated field.
I'd like to see 1000 in H7 instead of 900.

Screenshot


Rounded is a calculated field with formula =ROUND(Income; -2) for rounding to 100.

But the Grand Total for Rounded shows the sum for the original Income data (rounded afterward to 900) instead of the sum of the calculated data above (that sums to 1000).

How to create a Grand Total for the calculated fields?

I'm interested in a solution preserving the Pivot Table references. Currently, I'm already creating sum manually, but that just won't get updated automatically as data rows get added.


Raw data for recreating table:

Order;Client;Income
1;Bruce;120
2;Bruce;180
3;Clark;80
4;Peter;20
5;Peter;40
6;Clark;50
7;Clark;80
8;Peter;110
9;Bruce;50
10;Clark;160
  • 1
    Could you share source sample data with us , help to recreate Pivot Table! – Rajesh S Mar 30 '19 at 9:28
  • Thanks for getting back, I'll prepare a full sample table soon. – Geri Mar 30 '19 at 11:41
  • @RajeshS I updated the question with a full example. Would be great if you could help, or even help to rephrase the question perhaps. – Geri Mar 30 '19 at 12:29
4
+50

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). I had to create another column with the rounded values, and then include that column in the pivot table:

enter image description here

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). If you need a calculation applied to each raw value prior to aggregating, do it outside of the pivot table.

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. So despite the column header label, the pivot table isn't performing the calculation you expect. The column label in your pivot table would more accurately be "Rounded of Sum".

Pivot tables are a fantastic tool for simple aggregations, but you need to be careful about the sequence in which Excel applies calculations vs. aggregating, especially when you apply calculated fields or even use aggregation functions that involve more complex calculations than simple summing or counting. 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.


To preserve some of the discussion in the comment thread:

There are limitations to what you can do in a pivot table. 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. You would need to do that externally. But that won't necessarily break everything every time the data changes (although you will need to trigger recalculation of the pivot table).

From 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. The best way to accomplish what you need may be combining calculations internal and external to the pivot table.

Add the Rounded calculation, like I did, as another data column that feeds the pivot table. This is easy to maintain as you add or modify client records. In the pivot table, keep your calculated rounding field since that displays the aggregations in the form you need. Add another field that's the SUM of the new Rounded data column, as in my example. The pivot table will then contain everything calculated both ways, so everything you need is there.

Then you can deal with the appearance. You can label the columns more descriptively, but if you don't want the new column visible, hide it. You can stick a cell reference to its total under the visible portion of the pivot table, or even recreate your own Grand Total row and hide the one generated by the pivot table.

|improve this answer|||||
  • Thanks! I considered this solution, but for the grouped client aggregations I need the original sum first, then round it, then add up those aggregations. It is just how the country tax regulations go in my country. – Geri Mar 31 '19 at 19:57
  • E.g in the example above both Clark and Peter have a fake value reported, as their respective rounded aggregations show false values. I can work the issue around utilizing both solutions at once (both having the calculated field and the per transaction rounding), but that just blows up my production table (with monthly aggregations) to huge size. – Geri Mar 31 '19 at 20:02
  • Still, I'd like to some custom aggregations, perhaps some scripting magic or similar. – Geri Mar 31 '19 at 20:03
  • @Geri, there are limitations to what you can do in a pivot table. 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. You would need to do that externally and then display that result under the pivot table. – fixer1234 Mar 31 '19 at 20:03
  • 1
    I think I'll just create an intermediate pivot table for grouping and rounding, without any grand total. Then use that table as the data source for the resulting pivot table with grand total. It could work automaticall without any manual tweak, and scales nicely with any data change. – Geri Apr 1 '19 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.