2007 Excel Pivot Table Average Dollars per Manager

Posted on 2014-08-21
Last Modified: 2014-08-22
I have a pivot table where it shows the Regional Manager, the Area Manager, the Direct Manager and then each associate for each Direct Manager.  Let's say that each associate incurred costs at X dollars.  The sum of X dollars for each associate is totaled for their respective Direct Manager, each Direct Manager's totals are summed for the Area Manager, and each Area Manager's totals are summed for each Regional Manager.

My question is this, is it possible to create a function or calculated field to determine the average cost per associate.

Example:  Regional Mgr    Area Mgr     Direct Mgr                                      Grand Total     $ per Associate
                                                                                       Associate A     $1                    $1                      
                                                                                       Associate B     $2                    $2
                                                                                       Associate C     $5                    $5
                                                                                       Associate D     $0                    $0
Direct Mgr Grand Total                                                                                                 $7                      $1.75

For a large pivot table, calculating this field at line level is tedious, and I didn't know if there was a way to create this function within the pivot table.  Or, is there another option I haven't thought about?

Once I am able to understand how to show the total cost per associate per Direct Manager, I can then apply that logic for each Area Manager's Direct Manager's "$ per Associate" amounts.

Question by:kristibigo
    LVL 27

    Accepted Solution

    Well, the sum of the above costs is $8, not $7.  So the average cost would be $2. :-)

    But that minor bit of math out of the way, does your source data contain only one row of data per associate?  If so, it might be possible to integrate these averages in the source table.  Otherwise, it may not be possible.  A calculated field for the Pivot Table probably can't determine the number of unique Associates for any given Direct Manager, not to mention Area or Regional managers.


    Author Comment

    Okay, that's what I was afraid of. I guess I just needed confirmation from what I was able to research.  (Thanks for the math correction - whipped out the question too fast, I suppose).

    Thank you for your response.
    LVL 27

    Expert Comment

    by:Glenn Ray
    I'm sorry I wasn't able to provide a solution.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now