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?