Percentages and pivot tables

Hi Dave

select any single cell in your Total column then from the Pivot Toolbar select Filed>Options/Percentage of total from the: Show data as box.

Dave
OzGrid Business Applications

David, you must provide a more detailed description
of your PivotTable to get a definitive answer to
your question. You say that your PivotTable has 4
columns. I suspect that you mean the data list
used by your PivotTable has 4 columns. If you
follow Dave's advice you'll end up with a % of
Costs for all persons rather than "a percentage of
the total costs for each person". The point is:
A precise solution requires a much better
description of your PivotTable. What fields are
in the COLUMN, ROW and DATA areas of your
PivotTable? It might be necessary to alter the
configuration of you PivotTable and/or data list
to obtain the results you want. Is that
acceptable?

Also...

David, it would also be helpful to supply a small
set of representative rows from your data list and
the hand-calculated results you expect. This will
guard against misinterpetation of your stated
objective.

OK Mark. Thanks.
The pivot table is part of a largish workbook that starts off with some data pulled in from another database. This data comprises:- LastName; FirstName;Project;JobStartDate;JobEndDate;SalaryCost. The number of lines for each person varies according to the number of projects worked on.

The object of the exercise is to produce a report so that the correct percentage of each person's salary is charged against each project each month.

By doing a series of 'if' formulae I have determined which month each person is working on each project and for how many days. I have then got the salary costs for each line.

All of these tables have columns for Names, Projects, Etc and then the 12 months.

From my salaries table I then created 12 pivot tables (one for each month).

So, my salaries table contains columns for LastName; FirstName;Project;TotalSalaries; and then 12 more columns for the months.

My pivot tables uses this data and, for August, uses the two name fields and the Project for the rows and the sum of Aug for the data; Aug for the page and no columns at all. It successfully reports the salaries for each person by project and a total salary for each person. I thought that it would be easy to turn that into percentages but I am struggling. Thanks for your help.

David

Does this solve your problem?

Let's assume that A1:D5 contains:

{"LastName"," FirstName","Project","Aug"
;"White","Mary","A123",100
;"White","Mary","B456",200
;"Greene","Tom","X789",300
;"Greene","Tom","A123",450}

Construct a PivotTable with LastName and FirstName
in the ROW area, Project in the COLUMN area, and
Aug in the DATA area. Right-click in the DATA area
and choose Field... to bring up the PivotTable Field
dialog for Sum of Aug. Click the Options>> button
and change the "Show data as:" drop-down to "% of row".
Click OK.

Previously, you asked "to report the costs for each
person by project as a percentage of the total costs
for each person." 40% of Tom's salary is allocated
to Project X789. 2/3 of Mary's is devoted to Project
B456. Slightly over half of all salaries is expended
on Project A123.

Is this what you wanted?

Re: Does this solve your problem?

Thanks again, but no, it didn't fix my problem. Every cell on the pivot table showed 0.00%. The other problem with it was that the large number of projects that I have caused the pivot table to be very wide indeed!

I am only interested in the % of each person's monthly salary by project. I don't need to know how much of all the salaries is allocated to a project.

So, for example, if my salaries table shows, as it does, the following:

"LastName","FirstName","Project","Salary"
"Black","John","AM4062","2911"
"Brown","David","AC1007","420"
"Brown","David","HE4181","420"
"Green","Anne","RQ8272","303"
"Green","Anne","HE4181","1816"
"Green","Anne","HE4185","303"
"Green","Anne"."RQ8276","605"

then I want the pivot table to report

100%
50%
50%
10%
60%
10%
20% respectively

Here two people are working on HE4181, but I am not concerned with that for this purpose.

Re: Does this solve your problem?

> Every cell on the pivot table showed 0.00%.

This shouldn't have happened with my data. If you
were using the data below I must ask if your salaries
were entered as text or numbers. A quoted value as
shown below usually implies that the data type is
text. If you try to sum text you'll get 0.

This aside, let's get down to basics. You don't seem
to believe that the recommendation that I made earlier
will produce the your expected percentage values (as
listed). In fact, my recommended PivotTable configuration
does produce those values, but not as a list in a
single column. I am of the belief (and, am sure that if
I'm wrong someone will take issue) that you can't produce
your desired values using a PivotTable without adopting
the configuration I've suggested. Keep in mind that
a PivotTable is a cross-tabulation, and you're seeking
a statistic that based on the intersection of project and
employee (hence the 2-dimensional table configuration).

Once this table is created you can "feed" the results back
to your original data list using the GETPIVOTDATA() function.
Let's suppose that the data you provided is entered into cells
A1:D8. And, you've created a PivotTable with LastName and
FirstName in the ROW area, Project in the COLUMN area, and
Salary in the DATA area (displayed as % of row using as
described earlier).

In cell E1 enter "Pct" as the column header, and the formula,
=GETPIVOTDATA(Pivot,LastName&" "&FirstName&" "&Project), into
cell E2 and copy down. (IMPORTANT!! Please note that this
formulation uses a defined name, Pivot, as its 1st argument.
To make this definition you'll need to click on the PivotTable
cell containing "Sum of Salary" and then choose the Insert Name
Define... menu command. Alternately, you could just use the cell
reference of the cell containing "Sum of Salary" as the 1st
argument.) As a final step you'll need to format cells E2:E8 as
0%.

Does this do what you want? Are you begining to see how PivotTables
can be used to derive values that would otherwise require complex
formulae?

Re: Does this solve your problem?

I agree with everything you said Mark. I wonder if David is looking for something other than a pivot table. Here is my suggestion:
Using David's sample data, combine first name and last name into new column "A" (insert new column, then this formula =C2&" "&B2 in cell A2 and copy down. Then in column "F" cell F2, insert the following formula and copy down:
=E2/SUMIF($A$2:$E$8,A2,$E$2:$E$8), and then format as percent.

Re: Does this solve your problem?

Mark, thanks ever so much for your help. I have now got what I wanted thanks to your getpivotdata formula. I could not have done it without you.

I have yet to try Bruce's idea, but will do so. Thank you both.

David

Re: Does this solve your problem?

Bruce's formula will indeed produce the values
that David is seeking. But there's still
considerable value to using a PivotTable for this
kind of summary. Now that we gotten beyond the
technical issues of creating the PivotTable itself
we should redirect our attention to David's data
list design. If I understood a earlier description
of this list the Salary Costs are separated into
monthly columns. This is what relational database
designers call a repeating group. The "group" is
the set of salaries cost and they are repeated over
time (months). To get the maximum benefit from an
Excel PivotTable the data list should be 1NF
(in 1st Normal Form). To achieve this degree of
normalization there cannot be any repeating groups.
It would be much better if David's list devoted
a single column to Month values and a single column
to the Salary Costs. This design results in many
more rows than before, but allows you to produce
a PivotTable that summarizes the Salary Costs by
Project and by quarter or year. Values from such
a PivotTable could not be mapped one-to-one to the
rows of the data list, and a comparable formula
would be considerably more complex.