11 Replies Latest reply on Sep 14, 2018 11:28 AM by Susan Hildebrant

# Divide an aggregated calculation by a table calculation

I am trying to calculation departmental utilization. I have calculated the total billable hours (aggregate) and total available hours (table calc based on headcount table calc). I need to divide the billable hours by the available hours, but I can't seem to make it work. Workbooks (twbx and xlsx) are attached. Any ideas?

Susan

• ###### 1. Re: Divide an aggregated calculation by a table calculation

Hi Susan

try this

it will return this

have a good weekend

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Divide an aggregated calculation by a table calculation

Normally you would just put that table calc on the Billable sheet, and use it for the math you want.

But here's the rub.  You have defined that table calc to run along Name1.

You don't have Name1 on the Billable sheet, and adding it there will change the behavior of that sheet.  (You do have it on your Available sheet.)

I'm just looking at this for the first time,. so I don't know if you really need that running along Name1, or what it's really doing for that matter.

But usually you just add the table calc to the sheet and use it on that sheet.

(BTW, a table calc needs to be calc'd on every sheet it gets used on.  You can't calc it on one sheet and use the values on another.  The "table" of a table calc is the table that is underlying the sheet where it's being used.)

1 of 1 people found this helpful
• ###### 3. Re: Divide an aggregated calculation by a table calculation

Hi Susan,

I took a different route. See if the attached and the below screenshot is what you're looking for?  Total Billable Hours / Available Hours [by] Dept.  I believe the ratios are correct.  Thx, Don

• ###### 4. Re: Divide an aggregated calculation by a table calculation

Thanks, Jim. Your calculation gets me closer than I have eve been. However, when I compare it to what has been done manually to date, it is off by a factor of 10. So, I see a value at 0.078, I should be seeing 0.780. Any ideas on why that is happening?

Susan Hildebrant

PMO Manager

BRIDGE Energy Group

Improving real-time operations

M: 508.364.3866

www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

• ###### 5. Re: Divide an aggregated calculation by a table calculation

Thanks, Joe. The Name1 is used in calculating the head count which is the basis of the denominator in this case. The billables are associated with Full Name. They are connected in the join.

Susan Hildebrant

PMO Manager

BRIDGE Energy Group

Improving real-time operations

M: 508.364.3866

www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

• ###### 6. Re: Divide an aggregated calculation by a table calculation

Thanks, Don. Unfortunately, the ratios are not correct. The available hours you show in your example are for one person, not the total department. I wish it had been that simple!

Susan Hildebrant

PMO Manager

BRIDGE Energy Group

Improving real-time operations

M: 508.364.3866

www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

• ###### 7. Re: Divide an aggregated calculation by a table calculation

HI Jim,

Actually, the factor is 13 which happens to be the number of weeks in a quarter. Not sure if that is coincidental or not.

Susan Hildebrant

PMO Manager

BRIDGE Energy Group

Improving real-time operations

M: 508.364.3866

www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

• ###### 8. Re: Divide an aggregated calculation by a table calculation

Jim,

I found an error in one o my calculations and the calculation works great!

I am still learning about table calculations and LOD. Seeing them used with my data gave me a better understanding of how they work.

Thanks to you and Joe and Don for the quick responses and help.

Susan

Susan Hildebrant

PMO Manager

BRIDGE Energy Group

Improving real-time operations

M: 508.364.3866

www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

• ###### 9. Re: Divide an aggregated calculation by a table calculation

Jim,

I need your help one more time. The calculation works great by week, but I really need it by month. I haven’t been able to figure it out. Any help/guidance would be greatly appreciated

Thanks,

Susan

• ###### 10. Re: Divide an aggregated calculation by a table calculation

please create a new post and attach your twbx workbook - I will look at it

thanks

Jim

• ###### 11. Re: Divide an aggregated calculation by a table calculation

Actually, I think the problem is how the Total Headcount (and Total Available Hours) calculate. I need to use AVERAGE headcount in the month. I can create that in the table Calc for both Total Headcount and Total Available Hours by adding the dimension Day of Weekending to the table calc.

I added two new worksheets one for the average headcount and one for the average available hours, so you can see the difference. It is most noticeable in the June-August time frame for row Delivery.

I am guessing the LOD needs to be tweaked, but I don’t know how. The LOD is named Utilization by Dept.

Thanks for looking at this.

Susan