cancel
Showing results for 
Search instead for 
Did you mean: 
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!

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

pranit828
Super User II
Super User II

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.