0
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?
that's what I want to do but I'm not sure how to put that into a pivot table as a measure. Basically, each day there might be additional tasks or names so I want to do this in a way that will automatically react to that. – Jon – 2012-06-08T15:30:08.797
Short of writing some functions in code I am not sure how to do in a pivot table. – Brad Patton – 2012-06-12T01:06:42.820
yeah I've come to the same conclusion. Since I'm pulling in data programatically anyway I'll update my app to spit out the information in a more useful manner. – Jon – 2012-06-14T12:35:03.200