cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Captain_Ricard
New Member

Addition of time in a day

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

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft
Microsoft

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

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

View solution in original post

4 REPLIES 4
v-jiascu-msft
Microsoft
Microsoft

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

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

View solution in original post

@v-jiascu-msft Dale

 

The Measure works well ! 

 

 

Here's a test :

Power BI.jpg

The measure is the column named Temps Moyen HHMM

 

Thank you again for you help

Have a nice day

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 

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

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors