Calculated Item in Pivot Table

  • Thread starter Thread starter Christian Staffe
  • Start date Start date
C

Christian Staffe

Hi,

I have created a simple pivot table which has rows like won, lost and a
calculated item hitrate = won/(lost+won), that is a percentage field. The
column represents differents entities of the company and I would like to
have a "grand total" column that shows me the figures for the entire
company. The problem is that either the 'grand total' feature of Excel or
even a computed item will also sum up the percentage field (hitrate) which
is clearly not what I want. How to do this ? In addition, is there a
solution that still works when a new column is added ?

Christian
 
You could turn off the Column totals for the pivot table.
Then, create another calculated item, named "Grand Total", that adds the
row items, e.g.
=Won+Lost
 
Hmm, I don't really understand. Here is how my pivot table should look like
:

A B C Company
Won 3 4 5 12
Lost 1 12 0 13
Hitrate 75% 25% 100% 48%

Won and Lost for entities "A", "B" and "C" come from my data list. Hitrate
is a calculated item computed as Won/(Won+Lost). My problem is to define
correctly the "Company" column. If defined as A+B+C, then the Won and Lost
rows will be correct but the hitrate will be 200% (75 + 25 + 100) and I want
the hitrate to be 12/(12+13) = 48%
 
Sorry, I misread your question.
Turn off the Row totals for the pivot table
For the Company field, create a calculated item, named "Grand Total",
that adds A+B+C
On the PivotTable toolbar, choose PivotTable>Formulas>Solve Order
Move the Grand Total formula to above the HitRate formula.

If you add new companies, you'll have to change the Grand Total formula
to include them.
 
Back
Top