Duration Troubles

[heidibb]

Hello,

I am working on a project around runs, the duration for each run in hh:mm:ss and the pace (duration/total miles). My data is being captured in a google sheet, where I have the column defined as duration and it is being entered as 0:25:00 for example for the total running time. 

When I pull the data in, it initially looks right and shows exactly how it shows within the google sheet, but when I apply and look at it in the data window, it's converted over to a decimal. I understand that this is showing as a portion of a day (where 25 minutes is about .01736 of a day), but I would like to keep the data so it shows as hh:mm:ss where I can then total up the total running time and the average pace per person, etc.

I have searched through the message board and I'm not seeing anything similar to this.

I should also say that I'm very new to Power BI and am using this project as my first to get familiar with the tool.

Thanks in advance!

[v-qiuyu-msft]

Hi @heidibb,

In your scenario, you can change data type of the field as time, see:

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[heidibb]

Thank you @v-qiuyu-msft! That helped with the format in my data, but it won't allow me to sum up the amount of time. It only allows me to count the number of rows per runner.

So for example, if Beth had the follow runs and durations in June, I want to sum up total Duration where the final result is 13 hours, 20 minutes, 23 seconds (as hh:mm:ss it would be 13:20:23).

Any thoughts?

Date	Runs	Miles	Duration	Pace
5/31/2016	1	3	0:26:00	0:08:40
6/1/2016	1	5	0:42:00	0:08:24
6/3/2016	1	5	0:42:00	0:08:24
6/4/2016	1	9	1:17:00	0:08:33
6/5/2016	1	5	0:42:00	0:08:24
6/7/2016	1	4	0:34:00	0:08:30
6/8/2016	1	5	0:44:00	0:08:48
6/10/2016	1	4	0:37:00	0:09:15
6/11/2016	1	7	0:59:00	0:08:26
6/12/2016	1	6	1:10:00	0:11:40
6/13/2016	1	5	0:43:00	0:08:36
6/15/2016	1	5	0:42:00	0:08:24
6/17/2016	1	4	0:34:00	0:08:30
6/18/2016	1	8	1:09:00	0:08:38
6/19/2016	1	6	0:56:00	0:09:20
6/20/2016	1	5	0:43:00	0:08:36
6/21/2016	1	5	0:40:43	0:08:09

[Vvelarde]

hi @heidibb

Create a measure:

Total-Duration = FORMAT(SUm(RUNS[Duration]);"HH:MM:SS")

is : SS (with no space)

[heidibb]

Thank you @Vvelarde!! this is almost there! Final question, the column totals are calculating a bit weird. Everything else looks great, but not sure how to address the column totals

[heidibb]

I realize the grand totals for both months are off by 48 hours each, so it seems once it passes increments of 24 hours, it's dropping off. is there a way to just continue to total the hours indefinitly? This is the aggregation as it shows in excel. trying to replicate in Power BI.

	5	6	Grand Total
Anna	0:56:00	4:06:02	5:02:02
Beth	0:26:00	12:54:43	13:20:43
Brian	0:29:00	8:07:20	8:36:20
Christian		0:33:55	0:33:55
Erin	0:45:00	5:15:00	6:00:00
Heidi		3:32:03	3:32:03
Jeremy	0:26:00	3:29:29	3:55:29
Jillian	0:32:09	7:18:20	7:50:29
Josh	0:25:50	6:55:00	7:20:50
Justin	0:25:55	1:05:31	1:31:26
Michelle		3:16:30	3:16:30
Grand Total	4:25:54	56:33:53	60:59:47

[Vvelarde]

Hi @heidibb

Try this measure (hope it works)

Duration-RUNNER =
VAR DurationRUNNER =
    SUM ( Runners[Duration] )
RETURN
    IF (
        ROUNDDOWN ( DurationRUNNER; 0 ) > 1;
        ROUNDDOWN ( DurationRUNNER; 0 ) * 24
            + HOUR ( DurationRUNNER )
            & ":"
            & (
                MINUTE ( DurationRUNNER ) & ":"
                    & SECOND ( DurationRUNNER )
            );
        FORMAT ( DurationRUNNER; "hh:mm:ss" )
    )

[heidibb · Accepted solution]

@Vvelarde YES, this worked almost perfectly!! I had to make a couple tweeks, changing your semi-colons to commas and had to fix the minute & seconds sections to account for leading zeros when we have single digits (see bolded section). If you see anything off with this, let me know, but I can't thank you enough!!

DurationRUNNER =
VAR DurationRUNNER =
SUM ( RTB_Runs[Duration] )
RETURN
IF (
ROUNDDOWN ( DurationRUNNER, 0 ) > 1,
ROUNDDOWN ( DurationRUNNER, 0 ) * 24
+ HOUR ( DurationRUNNER )
& ":"
& (if(len(minute(DurationRUNNER)) > 1,minute(DurationRUNNER), "0" & minute(DurationRUNNER))
& ":"
& (if(len(second(DurationRUNNER)) > 1,second(DurationRUNNER), "0" & second(DurationRUNNER))
)),
FORMAT ( DurationRUNNER, "hh:mm:ss" )
)