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:
<tbody>
</tbody>
I made the following pivot:
<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.
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 | |||||
| PersonID | Name | Title | |||
| 1 | John | Manager | |||
| 2 | Jane | Manager | |||
| 3 | Tom | Analyst | |||
| Work | |||||
| WorkID | PersonID | AssignmentID | Work | Day | Capacity |
| 1 | 1 | C | 1.6 | 2/6/2017 | 8 |
| 2 | 2 | A | 1.6 | 2/7/2017 | 8 |
| 3 | 3 | B | 1.6 | 2/8/2017 | 8 |
| 4 | 1 | C | 1.6 | 2/9/2017 | 8 |
| 5 | 2 | A | 1.6 | 2/10/2017 | 8 |
| 6 | 3 | B | 3.2 | 3/6/2017 | 8 |
| 7 | 1 | C | 3.2 | 3/7/2017 | 8 |
| 8 | 2 | A | 3.2 | 3/8/2017 | 8 |
| 9 | 3 | B | 3.2 | 3/9/2017 | 8 |
| 10 | 1 | C | 3.2 | 3/10/2017 | 8 |
| 11 | 2 | A | 8 | 4/3/2017 | 8 |
| 12 | 3 | B | 8 | 4/4/2017 | 8 |
| 13 | 1 | C | 8 | 4/5/2017 | 8 |
| 14 | 2 | A | 8 | 4/6/2017 | 8 |
| 15 | 3 | B | 8 | 4/7/2017 | 8 |
| Assignments | |||||
| AssignmentID | AssignmentName | ||||
| A | Administrative | ||||
| B | Project 1 | ||||
| C | Project 2 | ||||
| Time | |||||
| Day | Month | Quarter | |||
| 2/6/2017 | 02-Feb | 1 | |||
| 2/7/2017 | 02-Feb | 1 | |||
| 2/8/2017 | 02-Feb | 1 | |||
| 2/9/2017 | 02-Feb | 1 | |||
| 2/10/2017 | 02-Feb | 1 | |||
| 3/6/2017 | 03-Mar | 1 | |||
| 3/7/2017 | 03-Mar | 1 | |||
| 3/8/2017 | 03-Mar | 1 | |||
| 3/9/2017 | 03-Mar | 1 | |||
| 3/10/2017 | 03-Mar | 1 | |||
| 4/3/2017 | 04-Apr | 2 | |||
| 4/4/2017 | 04-Apr | 2 | |||
| 4/5/2017 | 04-Apr | 2 | |||
| 4/6/2017 | 04-Apr | 2 | |||
| 4/7/2017 | 04-Apr | 2 |
<tbody>
</tbody>
I made the following pivot:
| Sum of Work | M | |||
| 02-Feb | 03-Mar | 04-Apr | Grand Total | |
| Row Labels | ||||
| Administrative | 3.2 | 3.2 | 16 | 22.4 |
| Jane | 3.2 | 3.2 | 16 | 22.4 |
| Project 1 | 1.6 | 6.4 | 16 | 24 |
| Tom | 1.6 | 6.4 | 16 | 24 |
| Project 2 | 3.2 | 6.4 | 8 | 17.6 |
| John | 3.2 | 6.4 | 8 | 17.6 |
| Grand Total | 8 | 16 | 40 | 64 |
<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.
