Average no of calls per hour vs per week

[Dunner2020]

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?

[igrandey89 · Accepted solution]

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.