Pivot table question

[richmarin]

I wanted to create a pivot table like the result below.

The important thing is that I have percentages at the end of each
column and
the totals end up at the end.


Item 1 Item 2 Total Items sold

Customer A 6 Sold 60% 4 Sold 40% 10 Sold


Customer B 50 Sold 50% 50 Sold 50% 100 Sold


How can I do the above?

 

[Roger Govier]

Hi

You can almost do what you want.
Provided you can accept

Customer Item 1 Item2 %Item1 %Item 2 Total
CustA 6 4 60% 40% 10
CustB 50 50 50% 50% 100

Drag Customer to Row Area, drag Item to Column Area.
Drag Quatity to data area twice.
Double click on the second quantity field in Layout dialogue, and choose
Options>Show data as> select % of Row from dropdown.

 

[Debra Dalgleish]

And on the worksheet, if you drag the Data field button to the left of
the Item field button, the percentage will follow the quantity for each
item.

 

[Roger Govier]

Hi Deb

And I played around with the PT for ages trying to do that. Never
thought about moving the columns in the source data!!!
And I thought I was pretty smart with Pivot Tables.
Just goes to show that I am in dire need of your forthcoming book on
using Pivot Tables.
When will it reach the shelves?
I had better put my order in with Amazon right now<bg>

 

[Debra Dalgleish]

Hi Roger,

I meant to move the Data field button to the left of the Item field
button in the column area of the pivot table.

When you first create the pivot table with two copies of the quantity
field, the data may appear vertically, with the Data field button in the
Row area. If you drag the Data button to the column area, it may end up
to the left of the Item button. After it's in the column area, you can
drag it to the right, and the layout changes.

Try that while you wait a couple of months for the book. <g>

Deb

 

[Roger Govier]

Hi Deb

I thought I had tried that and it didn't work. That's why I then thought
you meant to move the source data columns.
On retrying, you are absolutely right (when aren't you!!<vbg>) it does
exactly as described.

I now see you have the book listed on your site, with a March 6th
publication date.
Should I build a countdown on my Excel sheet?<g>.