Pivot: average of counts for multiple criteria

[Hurleyaw]

I have a fairly simple pivot table which shows call center data in a very useful and intuitive way for managers. Below is the sum of the number of calls for a given hour and day of the week in March. What I need to show is the average of the calls received for a given hour & weekday (word problem: "how many calls did we average for the 9:00 - 9:59 hour for all Mondays in March?"). I can and have done this with formulas, but I'm at a loss as to how it could be done with a pivot table, and I'd like to use a pivot table if possible, for speed and flexibility.

My source data looks like this, which is how I'm able to show the "hours" in column A like that.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88"]ACD Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Agent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]Call type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 148"]Start Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, width: 100"]End Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl65, width: 134"]Called/Calling No.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]ACD DID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl65, width: 99"]WaitTime(s)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]HoldTime(s)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="class: xl65, width: 94"]TalkTime(s)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl65, width: 62"]Hour[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl65, width: 62"]Day[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Month[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Call Week[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]Call Year[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Support Queue[/TD]
[TD]Bob Lastname[/TD]
[TD]Inbound[/TD]
[TD]1/1/2018 7:01:52 AM[/TD]
[TD]1/1/2018 7:01:53 AM[/TD]
[TD]123-456-7890[/TD]
[TD]N/A[/TD]
[TD]00:00:00[/TD]
[TD]00:00:00[/TD]
[TD]00:00:01[/TD]
[TD]=INT(TEXT(D2, "MM/DD/YYYY"))[/TD]
[TD]=(TEXT(D2, "hh"))+0[/TD]
[TD]=TEXT(K2, "DDD")[/TD]
[TD]=TEXT(D2, "mmm")[/TD]
[TD]=WEEKNUM(D2, 21)[/TD]
[TD]=YEAR(K2)[/TD]
[/TR]
</tbody>[/TABLE]

I think what I need to do is create a "Custom measure" for my data set, but I'm stuck there. Any help would be appreciated.

Thanks,

Andy

 

[Aryatect]

Can you add a helper column with value 1 in each row and then apply average on that new column.

 

[Aryatect]

Or scratch that following below steps should solve the problem:
1. In Data model Add 1 Measure to Count all the rows [Count].
2. Add a Calculated column with concatenation of Date and Hour field
3. Create a Calculated Measure "Distinct Count=DISTINCTCOUNT([Date + Hour])"
4. Create Another Calculated Measure "Average= [Count]/[Distinct Count]"
5. Create a pivot from data model with hours on Rows, Days on Columns and Average in Values.

This should give you desired results

 

[Hurleyaw]

Aryatect,

Thanks so much for taking the time to help me out on this.

It seems to me that you are on the right track. However,

1. "Calculated Column" Do you mean a helper column in my data, or do you mean "calculated field?"
Unfortunately, I'm not able to add a calculated field, and I'm not sure why. the only result I found said I must be using OLAP data, but I don't think I am.... I mean, I just copied all this in from CSV files I got from our various systems. Therefore, I added a helper column that's just "=[date] & [hour] and I get a 6-digit number.

2. When attempting to add "Average" to values, it says that for my DISTINCT count, "A table of multiple values was supplied where a single value was expected"

 

[Aryatect]

Hi Hurleyaw,

I was talking about putting a new column in the powerpivot data model named "Date + Hour" where i concatenated both date and hour column =CONCATENATE([Date],[Hour]). So by doing this you wont need to add this in the CSV file. and it will show in the data model like "6/4/20193". You can use any delimiter to if you like.

Then Distinct count on this concatenated column will give you unique number of entries for Date and hour combined. I tried the same with a dummy data on my end and it worked. I used the same formula in Power Pivot for calculated measure with name "Distinct Count" and the formula will be "Distinct Count:=DISTINCTCOUNT([Date + Hour]) where [Date + Hour] is the concatenated column name. Then create a new calculated measure with name "Average" with formula "Average= [Count]/[Distinct Count]" where [Count] is the calculated measure for count of the row and [Distinct Count] we calculated above.

Do let me know in case I completely misunderstood your question

Aryatect,

Thanks so much for taking the time to help me out on this.

It seems to me that you are on the right track. However,

1. "Calculated Column" Do you mean a helper column in my data, or do you mean "calculated field?"
Unfortunately, I'm not able to add a calculated field, and I'm not sure why. the only result I found said I must be using OLAP data, but I don't think I am.... I mean, I just copied all this in from CSV files I got from our various systems. Therefore, I added a helper column that's just "=[date] & [hour] and I get a 6-digit number.

2. When attempting to add "Average" to values, it says that for my DISTINCT count, "A table of multiple values was supplied where a single value was expected"

 

[Hurleyaw]

Hi Hurleyaw,

I was talking about putting a new column in the powerpivot data model named "Date + Hour" where i concatenated both date and hour column =CONCATENATE([Date],[Hour]). So by doing this you wont need to add this in the CSV file. and it will show in the data model like "6/4/20193". You can use any delimiter to if you like.

Then Distinct count on this concatenated column will give you unique number of entries for Date and hour combined. I tried the same with a dummy data on my end and it worked. I used the same formula in Power Pivot for calculated measure with name "Distinct Count" and the formula will be "Distinct Count:=DISTINCTCOUNT([Date + Hour]) where [Date + Hour] is the concatenated column name. Then create a new calculated measure with name "Average" with formula "Average= [Count]/[Distinct Count]" where [Count] is the calculated measure for count of the row and [Distinct Count] we calculated above.

Do let me know in case I completely misunderstood your question

I'm not understanding what you mean by "putting a new column in the powerpivot data model." My data is all in one workbook, just on a different sheet. I can figure out how to add a new "measure" but under "Analyze / Fields, Items, & sets" the "Calculated Field" option is grayed out and I can't figure out why.

 

[SpillerBD]

We often assume a user is using O365 since that is kind of a bargain for those who like to stay up-to date with Office Applications.
If you are not on Office365, what year/version of Excel are you using?

Mike Girvin is a known author, educator an YouTuber. One of his playlist gets into the PowerQuery and PowerPivot.

 

[Hurleyaw]

We often assume a user is using O365 since that is kind of a bargain for those who like to stay up-to date with Office Applications.
If you are not on Office365, what year/version of Excel are you using?

Mike Girvin is a known author, educator an YouTuber. One of his playlist gets into the PowerQuery and PowerPivot.

I am using 365. I'll check this out and see what I can learn. Thanks!!

 

[Aryatect]

Sorry, that was my bad, I should have specified that this can be done through Power Pivot.