Addition of time in a day

[Captain_Ricard]

Hi All,

it is my first post as i have some difficulties to find an answer to my issue.

I am a service desk manager and i'am working on a power BI dashboard to replace the excel file i am using until now.

My issue is that when i want to add times, can have the sum when times are in numeric format. in date/time format i cannot have the sum only the min or max value :

here's a sample : 

Day              HHMM Entrants        Abandon_15s        Servis       Servis_30s        Talk time
16/10/2018  08:00        2               0                             2             2                       00:06:58
16/10/2018  09:00        5               0                             5             5                       00:09:33
16/10/2018  09:30        2               0                             2             1                       00:20:40
16/10/2018  10:00        4               0                             4             4                       00:02:31
16/10/2018  10:30        3               0                             3             3                       00:18:40
16/10/2018  11:00        1               0                             1             1                       00:09:43
16/10/2018  11:30        3               0                             3             3                       00:12:24
16/10/2018  13:30        3               0                             3             3                       00:04:56
16/10/2018  14:00        2               0                             2             2                       00:07:25
16/10/2018  15:00        1               0                             1             1                       00:03:16
16/10/2018  16:00        4               0                             4             4                       00:11:33
16/10/2018  16:30        1               0                             1             1                       00:17:39

my goal is to have the sum of talktime for a given day in order to calculate the average time of talk time. 

formula is talktime/answered calls. issue is that as my data are sliced into halfs hours i get an error (multiple values for a given day) 

question is how can i add the times for a day ?

thank you in advance for your help, and sorry if my english is not perfect, its not my native language

[v-jiascu-msft · Accepted solution]

Hi @Captain_Ricard,

There could be two solutions. Please check out the demo in the attachment.

1. Add a column and convert the times into seconds which is numeric values. The calculation could be easy.

2. Integrate the solution 1 in a measure. 

Measure =
VAR avg_seconds =
    DIVIDE (
        SUMX ( 'Table1', DATEDIFF ( TIME ( 0, 0, 0 ), [Talk time], SECOND ) ),
        COUNT ( Table1[Abandon_15s] ),
        9999
    )
VAR hours =
    INT ( DIVIDE ( avg_seconds, 3600 ) )
VAR minutes =
    INT ( DIVIDE ( MOD ( avg_seconds, 3600 ), 60 ) )
VAR seconds =
    INT ( MOD ( MOD ( avg_seconds, 3600 ), 60 ) )
RETURN
    hours & ":"
        & minutes
        & ":"
        & seconds

Best Regards,
Dale

[Captain_Ricard]

@v-jiascu-msft Dale

The Measure works well ! 

Here's a test :

The measure is the column named Temps Moyen HHMM

Thank you again for you help

Have a nice day

[Captain_Ricard]

Hi @Dale

I tried to convert into numerioc values, but in the report view the format still in numeric and not so "understandable", when i convert the column into HH:MM format there is no calculation, only the choice of min and max as stated before.

So il will try the second solution you give and build a measure and revert back here to tell you the results

thank you for the tip and the attached example !

regards 

[v-jiascu-msft]

Hi @Captain_Ricard,

I forgot to add the details. It's a new column with the formula.

Seconds = DATEDIFF(time(0, 0, 0), [Talk time], SECOND)

Best Regards,
Dale