Percentage of work over time

[mvelarde1017]

Hi Experts,

I am having trouble displaying work as a percentage over time. I tried several things but I can't seem to figure it out.

I have the following tables:

[TABLE="width: 541"]
<tbody>[TR]
[TD="colspan: 3"]People[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PersonID[/TD]
[TD]Name[/TD]
[TD]Title[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John[/TD]
[TD]Manager[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane[/TD]
[TD]Manager[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tom[/TD]
[TD]Analyst[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Work[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WorkID[/TD]
[TD]PersonID[/TD]
[TD]AssignmentID[/TD]
[TD]Work[/TD]
[TD]Day[/TD]
[TD]Capacity[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD]1.6[/TD]
[TD]2/6/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]1.6[/TD]
[TD]2/7/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]B[/TD]
[TD]1.6[/TD]
[TD]2/8/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD]1.6[/TD]
[TD]2/9/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]1.6[/TD]
[TD]2/10/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3[/TD]
[TD]B[/TD]
[TD]3.2[/TD]
[TD]3/6/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD]3.2[/TD]
[TD]3/7/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]3.2[/TD]
[TD]3/8/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3[/TD]
[TD]B[/TD]
[TD]3.2[/TD]
[TD]3/9/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD]3.2[/TD]
[TD]3/10/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]8[/TD]
[TD]4/3/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]3[/TD]
[TD]B[/TD]
[TD]8[/TD]
[TD]4/4/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD]8[/TD]
[TD]4/5/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]8[/TD]
[TD]4/6/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]3[/TD]
[TD]B[/TD]
[TD]8[/TD]
[TD]4/7/2017[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Assignments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AssignmentID[/TD]
[TD]AssignmentName[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Administrative[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Project 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Project 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]Month[/TD]
[TD]Quarter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/6/2017[/TD]
[TD]02-Feb[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/7/2017[/TD]
[TD]02-Feb[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/8/2017[/TD]
[TD]02-Feb[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/9/2017[/TD]
[TD]02-Feb[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/10/2017[/TD]
[TD]02-Feb[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/6/2017[/TD]
[TD]03-Mar[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/7/2017[/TD]
[TD]03-Mar[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/8/2017[/TD]
[TD]03-Mar[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/9/2017[/TD]
[TD]03-Mar[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/10/2017[/TD]
[TD]03-Mar[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/3/2017[/TD]
[TD]04-Apr[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/4/2017[/TD]
[TD]04-Apr[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/5/2017[/TD]
[TD]04-Apr[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/6/2017[/TD]
[TD]04-Apr[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/7/2017[/TD]
[TD]04-Apr[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I made the following pivot:

[TABLE="width: 380"]
<tbody>[TR]
[TD]Sum of Work[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]02-Feb[/TD]
[TD]03-Mar[/TD]
[TD]04-Apr[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Administrative[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]22.4[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]22.4[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD="align: right"]1.6[/TD]
[TD="align: right"]6.4[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD="align: right"]1.6[/TD]
[TD="align: right"]6.4[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]6.4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]17.6[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]6.4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]17.6[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]64[/TD]
[/TR]
</tbody>[/TABLE]


In the pivot above I can also see the work done on a day, month or quarter basis for each person. For columns, I put Month and Day. For rows I have AssignmentName and Name. For values I have Sum of Work.

What I'd like to do is look at the work done as a percentage of an 8 hour work day. So for example, Jane would show 20% for 2/7/2017 (1.6 hrs/8 hrs); 2% for February (3.2 hrs/160 hrs); etc. I realize capacity is different for each month and that 160 hrs happens to be the total number of work hours for February.

I believe I need a running total of capacity to be able to calculate the percentage but I am not sure where to put the Capacity information (in column, row, values) and how to calculate percentage of work from that. There may be an easier way but I can't see it.

Any help you can provide would be greatly appreciated. Thanks in advance.

 

[mvelarde1017]

Anyone have any thoughts or suggestions?