Solved

Pivot Table Formula and column sum

Posted on 2014-01-25
4
1,158 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 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

773 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