- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Average no of calls per hour vs per week

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Average no of calls per hour vs per week

03-21-2023
02:24 PM

I got a data about no of calls handled and time taken by those calls in 15 mins time slot. Now I want to calculate the average no of calls taken and average time taken by calls and show those measures in the form of line graphs. I used following two measures:

Average no of calls per hour =

Averagex(

SUMMARIZE(ADDCOLUMNS(Fact_table, "Hour",HOUR(Fact_Table[Time]), "Date",Fact_Table[Start Date]), [Hour], [Date], "Calls attended", SUM(Fact_Table[Contacts handled]) ), [Calls attended] )

Average time taken by calls per hour =

Averagex(

SUMMARIZE(

ADDCOLUMNS(Fact_Table, "Hour",HOUR(Fact_Table[Time]), "Date",Fact_Table[Start Date]),

[Hour], [Date], "call Time",DIVIDE(SUM(Fact_Table[Contact handle time (mins)]),SUM(Fact_Table[Contacts handled]))),

[call Time] )

I showed these two measures as line graphs where x_axis contains the 'No of hours' in a day (i.e. 10 am, 11 am ....... so on) and worked fine.

I used again these measures as line graphs now x_axis contains 'No of weeks' in a year (i.e. Week 1, Week 2, Week 3, .... so on). I am not sure whether above measures can be shown to display the average no of calls taken in Week 1, Week 2, and so on.

My question is would above measures would work to display average no of calls taken at the week level? if not , is there any way of calculating it at the week level?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-21-2023
05:15 PM

The measures you have created for average no of calls per hour and average time taken by calls per hour are calculated based on the hourly data. Therefore, if you want to show these measures at the week level, you need to modify these measures to calculate the average no of calls taken and average time taken by calls per week.

To calculate the average no of calls taken per week, you can modify your existing measure as follows:

`Average no of calls per week = AVERAGEX( SUMMARIZE( ADDCOLUMNS( Fact_table, "Week Number", WEEKNUM(Fact_Table[Start Date]), "Year", YEAR(Fact_Table[Start Date]) ), [Week Number], [Year], "Calls attended", SUM(Fact_Table[Contacts handled]) ), [Calls attended] )`

In this measure, you are using the WEEKNUM and YEAR functions to get the week number and year from the Start Date column in your Fact_table. Then, you are summarizing the data by week number and year, and calculating the sum of contacts handled for each week. Finally, you are using AVERAGEX to calculate the average no of calls taken per week.

Similarly, to calculate the average time taken by calls per week, you can modify your existing measure as follows:

`Average time taken by calls per week = AVERAGEX( SUMMARIZE( ADDCOLUMNS( Fact_table, "Week Number", WEEKNUM(Fact_Table[Start Date]), "Year", YEAR(Fact_Table[Start Date]) ), [Week Number], [Year], "Total Time", SUM(Fact_Table[Contact handle time (mins)]), "Total Calls", SUM(Fact_Table[Contacts handled]) ), DIVIDE([Total Time], [Total Calls]) )`

In this measure, you are using the WEEKNUM and YEAR functions to get the week number and year from the Start Date column in your Fact_table. Then, you are summarizing the data by week number and year, and calculating the sum of contact handle time and contacts handled for each week. Finally, you are using AVERAGEX and the DIVIDE function to calculate the average time taken by calls per week.

1 REPLY 1

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-21-2023
05:15 PM

The measures you have created for average no of calls per hour and average time taken by calls per hour are calculated based on the hourly data. Therefore, if you want to show these measures at the week level, you need to modify these measures to calculate the average no of calls taken and average time taken by calls per week.

To calculate the average no of calls taken per week, you can modify your existing measure as follows:

`Average no of calls per week = AVERAGEX( SUMMARIZE( ADDCOLUMNS( Fact_table, "Week Number", WEEKNUM(Fact_Table[Start Date]), "Year", YEAR(Fact_Table[Start Date]) ), [Week Number], [Year], "Calls attended", SUM(Fact_Table[Contacts handled]) ), [Calls attended] )`

In this measure, you are using the WEEKNUM and YEAR functions to get the week number and year from the Start Date column in your Fact_table. Then, you are summarizing the data by week number and year, and calculating the sum of contacts handled for each week. Finally, you are using AVERAGEX to calculate the average no of calls taken per week.

Similarly, to calculate the average time taken by calls per week, you can modify your existing measure as follows:

`Average time taken by calls per week = AVERAGEX( SUMMARIZE( ADDCOLUMNS( Fact_table, "Week Number", WEEKNUM(Fact_Table[Start Date]), "Year", YEAR(Fact_Table[Start Date]) ), [Week Number], [Year], "Total Time", SUM(Fact_Table[Contact handle time (mins)]), "Total Calls", SUM(Fact_Table[Contacts handled]) ), DIVIDE([Total Time], [Total Calls]) )`

In this measure, you are using the WEEKNUM and YEAR functions to get the week number and year from the Start Date column in your Fact_table. Then, you are summarizing the data by week number and year, and calculating the sum of contact handle time and contacts handled for each week. Finally, you are using AVERAGEX and the DIVIDE function to calculate the average time taken by calls per week.

Announcements

Check out the October 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

User | Count |
---|---|

113 | |

108 | |

107 | |

92 | |

67 |

Top Kudoed Authors

User | Count |
---|---|

162 | |

133 | |

132 | |

93 | |

91 |