call center duration

[llyons]

Hello,  I have Excel data type Custom Column Number h:nn:ss from a power pivot that I have imported into Power BI. The field with duration imports as ABC 123 with data showing as 12/31/1899 12:00:01 AM. I have split the column to remove 12/31/1899 and I am let with 12:00:01 AM type is time (I do not have any PM at the end of the time). How do I convert 12:00:01 AM to use in a chart to show duration of an average time it took to answer a call? In 12:00:01 AM the call took 1 second to answer the call, another example is 12:16:29 AM the call took 16 minutes and 29 seconds to answer the call.

I want a line chart that shows interval as the axis, the duration of example minutes and seconds to be the value,  the legend would be the agent

also I want to use the duration in a table or card showing the the total average time it took to answer in the month of say September . For call speed to answer in September an example is average speed of answer is 5 minutes 30 seconds or  05:30

I would like to be able to do this with daily total, MTD and YTD.

can this be done?

[Greg_Deckler · Accepted solution]

@llyons - I have some articles on this that I think you will find helpful:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279#M92

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

[wac1ldy]

I also have a similar issue. I have a column of seconds. I pull that and others into a Matrix visualization and it allows me to aggregate or average and thay all appear in their respective columns and they filter just fine. Trouble is, I would like to show the data in the columns as hh:nn:ss. I can add measures etc. but when I pull the data into the Matrix, it will not allow an average, only a First etc. Anyone got any thoughts?

Thanks.

CW

[v-alq-msft · Accepted solution]

Hi, @llyons 

Based on your desciprion, I created data to reproduce your scenario.The pbix file is attached in the end.

Table:

You may create a custom column as below in 'Query Editor'.

= Table.AddColumn(#"Changed Type", "Custom", each let dur=[End]-[Start]
in
#time(Duration.Hours(dur),Duration.Minutes(dur),Duration.Seconds(dur)))

In Power BI Desktop, you may create a calculated column as below to calculate total seconds.

TotalSeconds = HOUR([Custom])*3600+MINUTE([Custom])*60+SECOND([Custom])

Finally you may format the column as below.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[llyons]

Thank you to everyone, especially Allan that gave me a piece that connected the dots for me. I am doing a happy dance right now! and will quickly finish up my project.  Have an awesome day

[Greg_Deckler · Accepted solution]

@llyons - I have some articles on this that I think you will find helpful:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279#M92

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

[llyons]

I am a new user and have the July 2020 version of Power BI. I do not have VAR Duration.

What I was able to do is create a column with the total amount of seconds (from my original example for one row 12:00:01 AM) but now I need to convert the seconds into  time formate. I found dax Time (= TIME([intHours],[intMinutes],[intSeconds])  ) but as a new user I do not understand how to write the code to make it work. How would I get the formula = TIME([intHours],[intMinutes],[intSeconds])   to refer to my column that has the total amount of seconds for a call that I need converted to time format?  Thank you for all of your time to help me understand the time function. I wish there was a simple way to do time like in Excel.

[MauriceMecowe]

Hi @llyons ,

If I were you I'd take the following steps.

- Either keep only the right left 5 characters of your column, so you lose the '12:' or subtract by the twelve hours leaving you with '00:16:29'
- Transform the time to seconds, so you can have it as an integer value

- From here, you can easily make calculations to have it in either minutes or hours

- Use the Time Intelligene functions you've mentioned to get the desired result.

Cheers,

Maurice

[llyons]

what are the formulas to do the steps you prepose,excluding the charts

[MauriceMecowe]

In order to keep the right 5 characters of a column:

RIGHT(Column, 5)

This will get you the time in seconds.

Column = MINUTE(Table[date time])*60+SECOND(Table[date time])

Divide this by 60 in order to get minutes etc.