Pivot Table Question - a puzzler

[Guest]

Friends,

I created a simple pivot table that is pulling values from four table
columns (LineOf Business, WeekEndDate, Salesmen_HeadCount, Salesmen_Quit). In
the pivot table, the aggregate function for Salesmen_HeadCount is Average()
and the aggregate function for Salesman_Quit is Sum().

Here is my problem: I now want to add a calculated field to the pivot table
that will return the Turnover Ratio, which should always be Salesman_Quit /
Average(Salesmen_HeadCount) no matter how I am viewing the data (i.e.,
grouped by LineOfBusiness, Grand Totals, etc.). For example, if the pivot
table shows 100 for Salesmen_HeadCount (which is the Average
ofSalesmen_HeadCount) and Salesman_Quit is 50, the Turnover Ratio should =
50/100 or 0.50.

But no matter how I construct the formula for Turnover Ratio calculated
field, the answer is wrong.

Anybody know how to make this work? Thanks ...

bill morgan

 

[Guest]

After experimenting with this, thought I would answer my own question - if
there's a better answer, please let me know:

I had to add a fifth column [Weeks] and post a value of 1 in each cell of
this column, and I changed the aggregate function for Salesmen_EndCount to
Sum().

I then added two calculated fields to the pivot table - Sum(weeks) called
[Weeks] and {Salesmen_EndCount / Sum(weeks)} called [AvgSalesmen] - this
second field returns the average number of salesman for each LineOfBusiness.

Finally I added a third calculated field called TurnoverRatio, which is
{Salesmen_Quit / [AvgSalesmen]}

The pivot works so long as all LinesOfBusiness are displayed inside the
pivot. If you remove LinesOfBusiness, the pivot naturally divides
Salesmen_EndCount by too many weeks, so the [AvgSalesmen] and [TurnRatio] are
wrong.

A little clunky, but fufills the requirements.

b.