Forum Discussion
How do I get hourly average?
Hi there,
I have tried to get an hourly average for the table below. I have multiple years' transactional records and below is a short version. I built the Year, Month, Date and Hour columns already:
| DateTime | Service Level | Call Volume | Year | Month | Date | Hour |
| 2/1/2020 8:00 | 100.00% | 1 | 2020 | February | 1 | 8 |
| 2/1/2020 8:15 | 100.00% | 1 | 2020 | February | 1 | 8 |
| 2/1/2020 8:30 | 100.00% | 1 | 2020 | February | 1 | 8 |
| 2/1/2020 8:45 | 100.00% | 2 | 2020 | February | 1 | 8 |
| 2/1/2020 9:00 | 66.67% | 3 | 2020 | February | 1 | 9 |
| 2/1/2020 9:15 | 100.00% | 2 | 2020 | February | 1 | 9 |
| 2/1/2020 9:30 | 0.00% | 0 | 2020 | February | 1 | 9 |
| 2/1/2020 9:45 | 100.00% | 4 | 2020 | February | 1 | 9 |
| 2/2/2020 8:30 | 100.00% | 1 | 2020 | February | 2 | 8 |
| 2/2/2020 8:45 | 100.00% | 2 | 2020 | February | 2 | 8 |
| 2/2/2020 9:15 | 100.00% | 2 | 2020 | February | 2 | 9 |
| 2/2/2020 9:30 | 100.00% | 2 | 2020 | February | 2 | 9 |
Eventually I need a table showing the average of call volumes by hour that I can easily fiter by year, month, date. I need to first aggregate the total call volume by hours/date/month/Year to get a table like below:
| Total Call Volume | Year | Month | Date | Hour |
| 8 | 2020 | February | 1 | 8 |
| 13 | 2020 | February | 1 | 9 |
Your help is much appreciated!!!
I ended up creating a separate table using the groupby function as I need the aggregated call volume specific for each hour/day/month/year. Something like this:
Table = GROUPBY('table name', 'table name'[Hour],'table name'[Date], 'table name'[Weekday],'table name'[Month],'table name'[Year],
"Calls Offered", SUMX(CURRENTGROUP(), 'Table Name'[Call Volume]))I added more columns to aggregate some other stats like talk time, AHT-HandleTime. It worked!
Hi wshao2 ,
Yes, your method is also a good choice, congratulations! In addition, the method I provided can also be used as a reference to provide another way of thinking for you to encounter problems in the future. Hope to help you😁.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi wshao2 ,
According to your needs, you might as well try to create the following measure: group and summarize the Call Volume value according to the Hour field.Total Call Volume = CALCULATE ( SUM ( 'Table'[Call Volume] ), FILTER ( ALL ( 'Table' ), 'Table'[Hour] = MAX ( 'Table'[Hour] ) ) )If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.I ended up creating a separate table using the groupby function as I need the aggregated call volume specific for each hour/day/month/year. Something like this:
Table = GROUPBY('table name', 'table name'[Hour],'table name'[Date], 'table name'[Weekday],'table name'[Month],'table name'[Year],
"Calls Offered", SUMX(CURRENTGROUP(), 'Table Name'[Call Volume]))I added more columns to aggregate some other stats like talk time, AHT-HandleTime. It worked!
Hi wshao2 ,
Yes, your method is also a good choice, congratulations! In addition, the method I provided can also be used as a reference to provide another way of thinking for you to encounter problems in the future. Hope to help you😁.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi wshao2
Create a measure
Calculate(SUM('table'[Call Volume], ALLEXCEPT('table','table'[Year],'table'[Month],'table'[Date],'table'[hour]))
