mvelarde1017

New Member
Joined
Mar 23, 2017
Messages
2
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:

People
PersonIDNameTitle
1JohnManager
2JaneManager
3TomAnalyst
Work
WorkIDPersonIDAssignmentIDWorkDayCapacity
11C1.62/6/20178
22A1.62/7/20178
33B1.62/8/20178
41C1.62/9/20178
52A1.62/10/20178
63B3.23/6/20178
71C3.23/7/20178
82A3.23/8/20178
93B3.23/9/20178
101C3.23/10/20178
112A84/3/20178
123B84/4/20178
131C84/5/20178
142A84/6/20178
153B84/7/20178
Assignments
AssignmentIDAssignmentName
AAdministrative
BProject 1
CProject 2
Time
DayMonthQuarter
2/6/201702-Feb1
2/7/201702-Feb1
2/8/201702-Feb1
2/9/201702-Feb1
2/10/201702-Feb1
3/6/201703-Mar1
3/7/201703-Mar1
3/8/201703-Mar1
3/9/201703-Mar1
3/10/201703-Mar1
4/3/201704-Apr2
4/4/201704-Apr2
4/5/201704-Apr2
4/6/201704-Apr2
4/7/201704-Apr2

<tbody>
</tbody>


I made the following pivot:

Sum of WorkM
02-Feb03-Mar04-AprGrand Total
Row Labels
Administrative3.23.21622.4
Jane3.23.21622.4
Project 11.66.41624
Tom1.66.41624
Project 23.26.4817.6
John3.26.4817.6
Grand Total8164064

<tbody>
</tbody>


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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Watch MrExcel Video

Forum statistics

Threads
1,129,430
Messages
5,636,232
Members
416,909
Latest member
hayleyg123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top