Results 1 to 7 of 7

Thread: Dynamic Average Selling Price Pivot
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow Dynamic Average Selling Price Pivot

    I have racked my brain with a solution for this problem and can never seem to crack it. I have to calculate Cost Per Head (CPH) for several different slices of a data set consisting of >500k rows. This is simplified, but suppose my data has a column for Month, Team, State, Account, Account Type, and the Amount. The Amount will either be the Total Cost for that particular intersection of Month/Team/State/Account or it will be the Total Salaried or Hourly Employees for that same intersection, determined by the Account Type column (Cost or Employee). See the table below.


    Month Team State Account Account Type Amount
    Jan A AZ Salaries Cost 50000
    Jan A AZ Benefits Cost 12500
    Jan A AZ Payroll Taxes Cost 20625
    Jan A AZ Salaried Employees Employee 5
    Jan A AZ Hourly Employees Employee 1
    Jan A NY Salaries Cost 120000
    Jan A NY Benefits Cost 30000
    Jan A NY Payroll Taxes Cost 52500
    Jan A NY Salaried Employees Employee 10
    Jan A NY Hourly Employees Employee 0
    Jan B CA Salaries Cost 80000
    Jan B CA Benefits Cost 20000
    Jan B CA Payroll Taxes Cost 35000
    Jan B CA Salaried Employees Employee 2
    Jan B CA Hourly Employees Employee 10
    Jan B NY Salaries Cost 170000
    Jan B NY Benefits Cost 42500
    Jan B NY Payroll Taxes Cost 63750
    Jan B NY Salaries Employees Employee 4
    Jan B NY Hourly Employees Employee 20


    I can calculate the CPH several different ways. I can create Sumifs formulas to pull all the Cost and all the Employees for whatever slice I want and then do the simple division. I can also create a Pivot Table to filter down to the Cost and Employees and then do the simple division formula off of the Pivot Table. However, I need to explore sooooo many different Accounts/Teams/Months/etc. that it is not feasible to set up so many Sumifs and the Pivot Table Method using a side formula just doesn't work as I explore different slices and the location of all the items in the Pivot Table changes.


    My ideal solution would be a Pivot Table that allows me to roll up or slice the data any way I want and be able to see columns for the Total Cost, Total Employees, and the CPH. See the table below:


    Month Jan
    Team All
    State NY


    Account Total Cost Total Employees CPH
    Salaries 290000 34 8529
    Benefits 72500 34 2132
    Payroll Taxes 116250 34 3419
    Grand Total 478750 34 14081


    I am pretty comfortable with Calculated Fields, Calculated Items, and VBA so any solutions are welcome!
    Last edited by bryanworkman; May 19th, 2019 at 03:22 AM.

  2. #2
    Board Regular
    Join Date
    Jan 2012
    Posts
    834
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Average Selling Price Pivot

    I am failing to see why a pivot doesn't work for this.

  3. #3
    New Member
    Join Date
    Oct 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Average Selling Price Pivot

    Quote Originally Posted by theBardd View Post
    I am failing to see why a pivot doesn't work for this.
    I totally know what you mean. It seems simple enough. The first problem is that my data for the number of employees is in the same column, thus the same pivot field, as my costs. So there's no way for me to take advantage of calculated fields. And I don't know of a way to make calculated items dynamic enough to capture the right CPH with whatever way I slice the data. So I've attempted a couple of things:

    1) I tried creating a new column and simply cutting all the amounts for the Employee line items and pasting them in my new column. This way I had a Cost Amount column and a Employee Amount column. This didn't help. I couldn't find a way to line up the costs and the number of employees still.

    2) I kept all the original amounts (both costs and employees) in the Amount Column and then created a new column which referenced the number of employees related to that cost amount. I accomplished this with a SUMIFS formula that adds up all the Headcount with the same characteristics as the cost. When I create a pivot table off of this and then created a calculated field to take the Amount column and divide it by new column I created, it worked! BUUUUUUT.... it only works at the lowest level of data for the accounts. If I try to create the pivot to see a total CPH for all accounts together I can't because it pulls the right costs but the number employees is multiplied by the number of accounts I'm trying to consolidate. For example, in my data if I try to pull the total CPH across all Teams/Regions/etc. then it shows my cost as $696,875 (which is right) but my number of employees as 156 (52 actual employees * 3 types of accounts). My real data has 90+ types of cost accounts and I need to roll them up to broader categories to identify trends or trouble spots. PLUUUUUUS... doing a SUMIFS formula on >500k rows of data with like 6 criteria took my PC about an hour to run. So not super efficient if I can avoid it.

    Let me know if that makes sense. I still don't have a simple solution with a pivot table.

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,910
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic Average Selling Price Pivot

    A possible solution


    Month
    Jan
    Team
    (All)
    State
    NY
    Account
    Sum of Amount
    Benefits
    72500
    Payroll Taxes
    116250
    Salaries
    290000
    Hourly Employees
    20
    Salaried Employees
    14
    Avg Benefits
    2132
    Avg Payroll Taxes
    3419
    Avg Salaries
    8529
    Avg Total
    14081


    Create 4 Calculate Items (gray area)

    Avg Benefits
    Formula =Benefits /('Hourly Employees' +'Salaried Employees' )

    Avg Payroll Taxes
    Formula ='Payroll Taxes' /('Hourly Employees' +'Salaried Employees' )

    Avg Salaries
    Formula =Salaries /('Hourly Employees' +'Salaried Employees' )

    Avg Total
    Formula =(Benefits +'Payroll Taxes' +Salaries )/('Hourly Employees' +'Salaried Employees' )

    Hope this helps

    M.

  5. #5
    New Member
    Join Date
    Oct 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Average Selling Price Pivot

    Quote Originally Posted by Marcelo Branco View Post
    A possible solution

    Create 4 Calculate Items (gray area)

    Avg Benefits
    Formula =Benefits /('Hourly Employees' +'Salaried Employees' )

    Avg Payroll Taxes
    Formula ='Payroll Taxes' /('Hourly Employees' +'Salaried Employees' )

    Avg Salaries
    Formula =Salaries /('Hourly Employees' +'Salaried Employees' )

    Avg Total
    Formula =(Benefits +'Payroll Taxes' +Salaries )/('Hourly Employees' +'Salaried Employees' )
    Thanks! The only issue is that I would have to set up and manually manage all of the calculated items. I have 90+ accounts. I would need to create a set of calculated a set of calculated items for each roll up of accounts as well. As I pull in new data for a new month, it may have a new account and I would need to do that check and then adjust all the calculated items to include it. I hope there's a more dynamic solution! But there may not be.

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,910
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic Average Selling Price Pivot

    Are you saying that there are 90+ accounts such as Benefits, Payroll Taxes, Salaries, ...., Account 90,...?

    If so, I'm not seeing (in a first thought) an automated solution with Excel - Pivot Table

    Access? (maybe, but I do not have enough knowledge/experience to suggest a solution)

    Maybe someone else can help you.

    M.

  7. #7
    New Member
    Join Date
    Oct 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Average Selling Price Pivot

    Quote Originally Posted by Marcelo Branco View Post
    Are you saying that there are 90+ accounts such as Benefits, Payroll Taxes, Salaries, ...., Account 90,...?
    That's right. For example, I have an account for Salaries, Vacations, Holidays, etc. and then I roll them all up into a group called Employee Compensation. I appreciate the ideas!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •