Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table Question - a puzzler
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 |
#2
|
|||
|
|||
Pivot Table Question - a puzzler
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. "bill_morgan" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating pivot table to include additional rows | Excel Discussion (Misc queries) | |||
Pivot Table Question | Excel Discussion (Misc queries) | |||
Pivot Tables, Help? | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
Jon Peltier - Pivot Table Result for yesterday's "Complex Chart" Question | Charts and Charting in Excel |