Pivot Table to roll up data and display in pivot chart

I'm using Excel 2010 to build a report on our service ticket data. In the end I want to find what the average time is per user for the date range that is selected.

I created a sample sheet with a pivot table, then manually summed up to create the column that I would like. I have tried to use "grand totals" but when i change it to average then the other columns change from sum to average. I think I may need to use a calculated field but I haven't been able to figure that out yet.

I would like to get the average column to be displayed in a pivot chart. I don't really need the daily time spent in the pivot chart.

Thanks in advance for your help!

Remove "Date" from the PivotTable and change the field settings of "Duration" from sum to average. To do this, double-click on "Duration" in Values, and select Value Field Settings. Select "Average" and click OK. Base your PivotChart on the new results.

I took your advice and compared the results to the average I had calcuated before. One of the results was not right because the average was the (sum of duration) / (count of items).
I need the average to be the (sum of duratoin) / (count of days). I realized I didn't have a very good example set of data so I expanded it so I had more entries per day. Then I tried to adjust the duration to by dividing it by the number of days and multiplying * the count (see attached). That way when the actual average is taken it by the pivot table it comes out correctly.

Is there a better way to do this? This seems a bit brute force and may not work well when I have 11k+ records but I'm not sure.

Originally Posted by Dionysos

Remove "Date" from the PivotTable and change the field settings of "Duration" from sum to average. To do this, double-click on "Duration" in Values, and select Value Field Settings. Select "Average" and click OK. Base your PivotChart on the new results.

Dawson64,

Not sure how practical this solution is, but I have incorporated code posted here:
http://lazyvba.blogspot.com/2010/11/...-to-count.html

This will count the distinct Date values for each name. The VBA code is stored on the sheet in which it is used. I thought I would let you know for reference in the event you can use it. Ihave also attached the modified xlsm for your review.