Pivot Table grand total across columns
- by Jon
I'm using Excel 2010 and Power Pivot. I'm trying to calculate confidence and velocity for a development team. I'm extracting some information from our time and defect system each day and building a data set. What I need to do with Excel is do the calculations. So each day I add to my data set 1 row per task in the current project, estimate for that task and the time spent on that task. What I want to calculate is the estimate/actual for each task but also for each person. The trouble is that each day the actual is cumulative so I need to pick out the maximum value for each task. The estimate should remain unchanged. I can make this work at the task level with a calculated measure (=MAX(worked)/MAX(estimate)) but I don't know how to total this up for a person. I need the sum of the max worked for each task.
So a dataset might look like:
Name Task Estimate Worked
N1 T1 3 1
N2 T2 3 1
N3 T3 4 1
N1 T1 3 2
N2 T4 5 1
N3 T3 4 2
N1 T5 1 2
N2 T6 2 3
N3 T7 3 2
What I want to see is for task T1 2 days were worked against an estimate of 3 days - so 2/3. For person N1 I want to see that they worked a total of 4 days against an estimate of 4 days so 4/4. For person N2 they worked 5 days for an estimate of 10 days.
Any ideas on how I can achieve this?
