97 Pivot
Posted by kristy on May 28, 2001 11:34 AM
Hi,
I am working with a pivot table and having trouble with the grand total of the %Diff (=Met/Plan) column. It is summing to 100% when I want to have it calculate Met/Plan which would be 50%. %Diff is a calculated item.
I tried using a calculated field and I couldn't get it to work either.
Count of Client Type
Milestone Met Plan %Diff
Decision 1 2 50%
Meeting 1 2 50%
Grand Total 2 4 100%
Client Type Milestone
A Plan Meeting
A Met Meeting
A Plan Decision
B Plan Meeting
B Plan Decision
B Met Decision
Any help would be appreciated!!!!
Thanks!
Kristy
Posted by Mark W. on May 30, 2001 8:59 AM
Kristy, the easiest way to get your Met/Plan
calculation into your PivotTable is the following:
1. Drag 'Type' field to the DATA area and re-Name
it to '% of Plan'
2. Press the [ Options >> ] button, set the "Show
data as" field to "% Of", set the "Base field" to
"Type", set the "Base item" to "Plan", and press
[ OK ].
3. After pressing the [ Finish ] button you'll
notice that this adds 2 additional columns labelled
'Met' and 'Plan'. The 'Met' column is equivalent
to Met/Plan, and obviously, the 'Plan' column is
Plan/Plan.
This extra column is the downside of this approach.
If you can't tolerate this extra column let me know
and will find another (more complex) approach that
will undoubtedly involve the addition of more
columns to your data set.
Posted by kristy on May 30, 2001 2:42 PM
Mark,
Thanks for your solution! I tried it, works great BUT my users don't like that extra column. They want that grand total line gone altogether until I find a solution!
They will be using drilldrown so they will see the extra columns, right? I think that's a small price to pay.
What would the extra columns have to be to get this to work?
TIA,
Kristy
, the easiest way to get your Met/Plan
Posted by Mark W. on May 31, 2001 8:20 AM
Kristy, I was too optimistic about an alternate
solution. Because you have 'Type' in the COLUMN
area both of its items, "Met" and "Plan", continue
to show. However, there is one more approach --
hide the extraneous "Plan" column, format the
cells in the column immediately to the right of
the PivotTable with a left-hand border, select
the "Met" label beneath the "Met/Plan" label and
format it as ;;; to hide its presence.
