# Pivot Table grand total across columns

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?

-

You can create a MAX-IF statement for each person:

``````=MAX(IF(A2:A10="N2", D2:D10))
``````

This will select only the rows where the name is "N2" and then apply the MAX function to those cells.

NOTE: You will need to hit Ctrl-Shift-Enter to get Excel to execute this formula otherwise you will get a #VALUE error in the cell.

-
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 Jun 8 '12 at 15:30
Short of writing some functions in code I am not sure how to do in a pivot table. – Brad Patton Jun 12 '12 at 1:06
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 Jun 14 '12 at 12:35