Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wshao2
Regular Visitor

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: 

 

DateTimeService LevelCall VolumeYearMonthDateHour
2/1/2020 8:00100.00%12020February18
2/1/2020 8:15100.00%12020February18
2/1/2020 8:30100.00%12020February18
2/1/2020 8:45100.00%22020February18
2/1/2020 9:0066.67%32020February19
2/1/2020 9:15100.00%22020February19
2/1/2020 9:300.00%02020February19
2/1/2020 9:45100.00%42020February19
2/2/2020 8:30100.00%12020February28
2/2/2020 8:45100.00%22020February28
2/2/2020 9:15100.00%22020February29
2/2/2020 9:30100.00%22020February29

 

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 VolumeYearMonthDateHour
82020February18
132020February19


Your help is much appreciated!!!

2 ACCEPTED SOLUTIONS

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!

View solution in original post

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.

View solution in original post

4 REPLIES 4
v-henryk-mstf
Community Support
Community Support

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.

 

v-henryk-mstf_0-1611797782369.png

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.

pranit828
Community Champion
Community Champion

Hi @wshao2 

Create a measure

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





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.