Solved

Pivot Table Formula and column sum

Posted on 2014-01-25
4
1,211 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

726 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