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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Dunner2020
Post Prodigy
Post Prodigy

Sum of calls should be same as average no of calls

I got call center data (i.e. 15 mins intervals) that starts from 27 October 2022 to 28 March 2023. I want to show the average no of call handled every week and want to show it against the no of weeks. I used the following measure to calculate the average no of calls.

Average Calls Per Week =
Averagex(
SUMMARIZE(Dates, Dates[Year], Dates[Week Number], "Calls attended", SUM(Sheet1[Contacts handled]) ),
[Calls attended] )

 

I showed it against week number on table visual as shown below:

Dunner2020_0-1680052887514.png

 

The total no of calls handled in the first week is 1924 and the average number of calls handled during the first week is 962. The puzzling bit for me is that there is only one week of data related to the first week (i.e. Janaury 2023) available, and there is no first-week data from other years. So the average of first-week calls handled should be the same as the sum of first-week calls handled as there is no first-week data from other years. Average measure just doing half of the total call attended in particular week. Not sure where I made mistake. Any help would be appreciated.

Sample Data here

2 REPLIES 2
amitchandak
Super User
Super User

@Dunner2020 , Join date without time stamp to a date table and have year week column there. Year and week need to be columns not expressions

 

Averagex(
SUMMARIZE(ADDCOLUMNS(Sheet1, DAte[Year]), Date[Week], "Calls attended", SUM(Sheet1[Contacts handled]) ),
[Calls attended] )

 

or

 

Averagex(
SUMMARIZE(ADDCOLUMNS(Sheet1, Sheet1[Year]), Sheet1[Week], "Calls attended", SUM(Sheet1[Contacts handled]) ),
[Calls attended] )

@amitchandak , addcolumn function does not let to add Date[Year] as column. ?????????

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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