How do I get hourly average?

[wshao2]

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!!!

[wshao2 · Accepted solution]

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!

[v-henryk-mstf]

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.

[wshao2 · Accepted solution]

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!

[v-henryk-mstf · Accepted solution]

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.

[pranit828]

Hi @wshao2 

Create a measure

Calculate(SUM('table'[Call Volume], ALLEXCEPT('table','table'[Year],'table'[Month],'table'[Date],'table'[hour]))