How to convert "5 mins 5 sec" format in column into numeric form?

[sjpbi]

I have a dataset with this columns and I need to aggregate the values in the outage duration. I need to get total outage duration for a certain date(last down) and the total overall outage duration.

By this dataset, I need to aggregate this so I can have a card visual to display total outage duration (1 Days 4 Hrs 28 Mins 19 Secs)

[ryan_mayu · Accepted solution]

@sjpbi 

you can do the data transform in PQ

1. replace hrs to 3600, replace mins to 60 and replace secs to 1

2. split the column by space

3. create a new column to get seconds

at last you can create a dax

Measure = 
VAR total=SUM('Table'[Custom])
VAR _day=INT(total/3600/24)
VAR _h=int((total-_day*3600*24)/3600)
VAR _min=INT((total-_day*3600*24-_h*3600)/60)
VAR _sec=int((total-_day*3600*24-_h*3600-_min*60))
return _day&" Days "&_h&" Hrs "&_min&" Mins "&_sec&" Secs"

the output is different from yours. could you pls double check your expected output?

pls see the attachment below

[Arul]

@sjpbi ,

Is it possible that hours can go beyond 24 hrs for each row?

[ryan_mayu · Accepted solution]

@sjpbi 

you can do the data transform in PQ

1. replace hrs to 3600, replace mins to 60 and replace secs to 1

2. split the column by space

3. create a new column to get seconds

at last you can create a dax

Measure = 
VAR total=SUM('Table'[Custom])
VAR _day=INT(total/3600/24)
VAR _h=int((total-_day*3600*24)/3600)
VAR _min=INT((total-_day*3600*24-_h*3600)/60)
VAR _sec=int((total-_day*3600*24-_h*3600-_min*60))
return _day&" Days "&_h&" Hrs "&_min&" Mins "&_sec&" Secs"

the output is different from yours. could you pls double check your expected output?

pls see the attachment below

[sjpbi · Accepted solution]

How about if there are in days in the column as well like "2 days 5 Mins 5 Secs"? What is the value to replace the days text?

[ryan_mayu]

then change days to 86400