Dynamic Average Selling Price Pivot

[bryanworkman]

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!

 

[theBardd]

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

 

[bryanworkman]

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.

 

[Marcelo Branco]

A possible solution


[TABLE="class: grid"]
<tbody>[TR]
[TD]

Month
​

[/TD]
[TD]

Jan​

[/TD]
[/TR]
[TR]
[TD]

Team
​

[/TD]
[TD]

(All)
​

[/TD]
[/TR]
[TR]
[TD]

State
​

[/TD]
[TD]

NY
​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Account
​

[/TD]
[TD]

Sum of Amount
​

[/TD]
[/TR]
[TR]
[TD]

Benefits
​

[/TD]
[TD]

72500
​

[/TD]
[/TR]
[TR]
[TD]

Payroll Taxes
​

[/TD]
[TD]

116250​

[/TD]
[/TR]
[TR]
[TD]

Salaries
​

[/TD]
[TD]

290000​

[/TD]
[/TR]
[TR]
[TD]

Hourly Employees
​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR]
[TD]

Salaried Employees
​

[/TD]
[TD]

14​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]

Avg Benefits
​

[/TD]
[TD="bgcolor: #D9D9D9"]

2132​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]

Avg Payroll Taxes
​

[/TD]
[TD="bgcolor: #D9D9D9"]

3419
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]

Avg Salaries
​

[/TD]
[TD="bgcolor: #D9D9D9"]

8529
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]

Avg Total
​

[/TD]
[TD="bgcolor: #D9D9D9"]

14081
​

[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[bryanworkman]

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.

 

[Marcelo Branco]

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.

 

[bryanworkman]

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!