?
Solved

Pivot Table Formula and column sum

Posted on 2014-01-25
4
Medium Priority
?
1,406 Views
Last Modified: 2014-01-25
In a Pivot Table suppose a calculated field C = A * B
How can I get a proper grand total of the field?
In the Grand Total row it produces SUM(A)*SUM(B) which is totally :) meaningless
But what is needed is SUM(C)
Regards
Brian
0
Comment
Question by:canesbr
  • 2
  • 2
4 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39809538
Unfortunately, this behavior of Calculated Fields in the Grand Total row is by design. It may not be the design you would like, but it is the design that Microsoft chose to implement.

As a workaround, if you add the C = A * B formula to the raw data (before creating a PivotTable), then it will sum C exactly as you expect.

Alternatively, if you use a SUMPRODUCT formula outside of the PivotTable, it too will work as you expect.
=SUMPRODUCT((A column =A)*(B colmn = B), Column being summed)
Note that when building the formula, you should type a cell address for A and B rather than clicking on the cells. Otherwise, Excel will use a GetPivotData function reference, which may not be what you want.
0
 

Author Comment

by:canesbr
ID: 39809552
Thank you, (I too would prefer to do it all using cell formulas) but my question was to find out how to do this purely in the PT. Does the "totalling" always follow the formula for the field?
If you are, for example, doing ratios (or %ages) and you have a PT formula field C=B/A then the "total" of Total(C)=Sum(B)/Sum(A) will be correct.
But in my OP example Total(C)=SUM(A)*SUM(B) is just wrong. The case in point is a simple quantity * price.
Ought there not to be a way to specify how you want "Totals" of calculated fields to work?
Regards
Brian
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 39809560
Brian,
There you go again, applying logic where logic was not invited. It won't end well.

:-)

I don't know if you have seen Microsoft Excel MVP Debra Dalgleish' discussion of calculated fields, but the problem you are describing is one she covers in detail--with exactly the same result as you describe. There is no setting that allows you to specify how you want the Total of a calculated field to be determined. Excel applies the same approach to the Total cell as it does to a cell in a Pivot row. http://www.contextures.com/excel-pivot-table-calculated-field.html
0
 

Author Comment

by:canesbr
ID: 39809583
Now I remember why I hate Pivot Tables.
Regards
Brian
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question