
1. Re: Divide an aggregated calculation by a table calculation
Jim Dehner Sep 7, 2018 2:15 PM (in response to Susan Hildebrant)1 of 1 people found this helpfulHi 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.

2. Re: Divide an aggregated calculation by a table calculation
Joe Oppelt Sep 7, 2018 2:24 PM (in response to Susan Hildebrant)1 of 1 people found this helpfulNormally 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.)

3. Re: Divide an aggregated calculation by a table calculation
Don Wise Sep 7, 2018 2:36 PM (in response to Susan Hildebrant)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
Susan Hildebrant Sep 7, 2018 3:49 PM (in response to Jim Dehner)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 realtime operations
M: 508.364.3866
SHildebrant@BridgeEnergyGroup.com<mailto:SHildebrant@BridgeEnergyGroup.com>
www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

image001.png 32.5 KB


5. Re: Divide an aggregated calculation by a table calculation
Susan Hildebrant Sep 7, 2018 3:50 PM (in response to Joe Oppelt)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 realtime operations
M: 508.364.3866
SHildebrant@BridgeEnergyGroup.com<mailto:SHildebrant@BridgeEnergyGroup.com>
www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

image001.png 32.5 KB


6. Re: Divide an aggregated calculation by a table calculation
Susan Hildebrant Sep 7, 2018 3:52 PM (in response to Don Wise)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 realtime operations
M: 508.364.3866
SHildebrant@BridgeEnergyGroup.com<mailto:SHildebrant@BridgeEnergyGroup.com>
www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

image001.png 32.5 KB


7. Re: Divide an aggregated calculation by a table calculation
Susan Hildebrant Sep 10, 2018 7:31 AM (in response to Jim Dehner)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 realtime operations
M: 508.364.3866
SHildebrant@BridgeEnergyGroup.com<mailto:SHildebrant@BridgeEnergyGroup.com>
www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

image001.png 32.5 KB


8. Re: Divide an aggregated calculation by a table calculation
Susan Hildebrant Sep 11, 2018 8:13 AM (in response to Jim Dehner)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 realtime operations
M: 508.364.3866
SHildebrant@BridgeEnergyGroup.com<mailto:SHildebrant@BridgeEnergyGroup.com>
www.BridgeEnergyGroup.com<http://www.bridgeenergygroup.com/>

image001.png 32.5 KB


9. Re: Divide an aggregated calculation by a table calculation
Susan Hildebrant Sep 14, 2018 10:29 AM (in response to Jim Dehner)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
Jim Dehner Sep 14, 2018 10:44 AM (in response to Susan Hildebrant)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
Susan Hildebrant Sep 14, 2018 11:28 AM (in response to Jim Dehner)Thanks for the quick reply.
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 JuneAugust 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

Reference Data1.xlsx 1.5 MB