Day of Week averages after summing totals by date

[jzeunik]

I'm working with call center data. I have call volumes for multiple groups by date, using the TEXT function to return the day of week. Like so:

06/03/19 Mon Group1 1000
06/03/19 Mon Group2 1400
06/04/19 Tue Group1 1100
06/04/19 Tue Group2 1500
06/10/19 Mon Group1 1300
06/10/19 Mon Group2 1000
06/11/19 Tue Group1 1100
06/11/19 Tue Group2 1600

I pull all of the data into a pivot table, and I want to calculate the AVERAGE total calls for Mondays, Tuesdays, etc. The average in the pivot table calculates the above based on 4 Mondays and 4 Tuesdays. So, I get a Monday average of 1175, when the true average is 2350.

 

[Joe4]

So, I get a Monday average of 1175, when the true average is 2350.

Not according to the data you have posted.
You do not have any values over 1600. So how can the average be 2350?

 

[jzeunik]

Not according to the data you have posted.
You do not have any values over 1600. So how can the average be 2350?

Sorry that I was unclear.

06/03/19 Mon Group1 1000
06/03/19 Mon Group2 1400
==================
MON 06/03 TOTAL 2400

06/10/19 Mon Group1 1300
06/10/19 Mon Group2 1000
==================
MON 06/10 TOTAL 2300

So, (2400 + 2300) / 2 Mondays = 2350 Avg. for Monday

 

[Joe4]

I see. That is a tricky one.
Not exactly sure how to get that.

I don't use Pivot Tables very much. Maybe someone who uses them a lot will have some ideas on how to accomplish that.