Pivot: average of counts for multiple criteria

Hurleyaw

New Member
Joined
Jun 18, 2019
Messages
4
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.

ACD Name

<tbody>
</tbody>
Agent

<tbody>
</tbody>
Call type

<tbody>
</tbody>
Start Time

<tbody>
</tbody>
End Time

<tbody>
</tbody>
Called/Calling No.

<tbody>
</tbody>
ACD DID

<tbody>
</tbody>
WaitTime(s)

<tbody>
</tbody>
HoldTime(s)

<tbody>
</tbody>
TalkTime(s)

<tbody>
</tbody>
Date

<tbody>
</tbody>
Hour

<tbody>
</tbody>
Day

<tbody>
</tbody>
Month

<tbody>
</tbody>
Call Week

<tbody>
</tbody>
Call Year

<tbody>
</tbody>
Support QueueBob LastnameInbound1/1/2018 7:01:52 AM1/1/2018 7:01:53 AM123-456-7890N/A00:00:0000:00:0000:00:01=INT(TEXT(D2, "MM/DD/YYYY"))=(TEXT(D2, "hh"))+0=TEXT(K2, "DDD")=TEXT(D2, "mmm")=WEEKNUM(D2, 21)=YEAR(K2)

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Can you add a helper column with value 1 in each row and then apply average on that new column.
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
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

New Member
Joined
Jun 18, 2019
Messages
4
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

Active Member
Joined
Jun 17, 2019
Messages
308
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

New Member
Joined
Jun 18, 2019
Messages
4
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,706
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

New Member
Joined
Jun 18, 2019
Messages
4
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

Active Member
Joined
Jun 17, 2019
Messages
308
Sorry, that was my bad, I should have specified that this can be done through Power Pivot.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,688
Messages
5,445,958
Members
405,372
Latest member
Vithanalas

This Week's Hot Topics

Top