Pivottable percentages

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using a pivottable to addup two columns of figures, Stock and Orders
pivotted by department. I want to show the percentage (%) of stock ordered,
by department by cant do it.

Can anyone show me please.

Thanks
Dean
 
Hi Dean

We need to see what your raw data looks like.
Post a small sample of the layout (not an attachment) and help should be
available.
I have to go off line now, but if there is no answer by the morning, I
will pick it up.
 
Roger,

an example would be like...

Dept Stock On Ord %
01 32332 157 0.485
03 15000 976 6.506
09 34000 25000 73.52

Stock and On Ord are figigures collected from MS Queries on seperate sheets.
The pivottable is used to add them up and display them next to the Dept. I
also want to show the percentage (%) of each department on order, to what is
in stock!

Dean
 
Hi Dean

In the PT,
drag Dept to the Row Area
drag Stock to the Data Area
drag On Ord to the Data Area
Ensure that both data items are set to Sum
On the resulting PT, drag the Data button and drop it on the word Total,
this will give your data layout side by side as opposed to one under the
other.

Now, from the PT Toolbar, use the PT dropdown to select
Formulas>Calculated Field
In the name box Type Percent
In the formula box use the Insert field button to create
= 'On Ord' /'Stock '

This will get added to the Pt as the third column of Data.
Go to the PT Wizard, choose Layout, click on the new calculated filed
Percent and change the name to % of Stock and change it's number format
to Percentage with 2 places of Decimal.

Change the field headings from Sum of Stock etc. to what you want, but
be aware that you cannot use the same name as the already existing filed
name. I sometimes change the headings in my source table to have an
exclamation mark after them, as I can then use the real title I want
when I format the PT Report.
 
Hi
Totally dyslexic this morning!!!

Where I used the word "filed" I meant to write "field"
 
Back
Top