Unable to reproduce average call time metric from excel in PowerBI

[pborah]

Hi,

I want to display a card with average call time for a call center.

I have time data as follows -

AnsweredTime (24h, HH:mm:ss), DisconnectedTime (24h, HH:mm:ss)

My formula for a calculated column is - 

Call Time = DATEDIFF(TransformedAll[AnsweredTime],TransformedAll[DisconnectedTime],SECOND)

I drop this column into a card and select the average of this column from Fields. There are two filters - only calls that have been answered - CallAnswered=True, and calls that have not been transferred to third party - CallTransferred=False. However the number that I get is very far off from what I get in excel. 

Here's what I'm doing in excel - 

Filter out all rows for calls that were not answered, or calls that were transferred, subtract DisconnectedTime from AnsweredTime, sum up the difference and divide it by the number of rows left after filtering. 

In excel I get the avg call time to be 00:02:28 or 148 seconds, in Power BI, my avg call time is 2.29 seconds. What am I doing wrong? Thank you.

[v-juanli-msft · Accepted solution]

Hi @pborah 

Create a column

diff = DATEDIFF(Sheet1[DisconnectedTime],Sheet1[AnsweredTime],SECOND)

Then create a measure

Measure = AVERAGEX(FILTER(Sheet1,Sheet1[CallAnswered]=TRUE()&&Sheet1[CallTransferred]=FALSE()),[diff])

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@pborah - See if this helps: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak]

@pborah , refer if these can help

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...