Calculate Time Data into Hours & Minutes using SUM

[littlepatos]

I am trying to show SUM of total hours for each activity

Hours & Minutes column can't be set as TIME so unsure how to show visual with aggregate SUM

TimeHours & MinutesMinutesMinjutes as ValuesTime Spent

0:05:32	00:05	05	5	Over 40
1:00:18	01:00	60	60	Over 40
0:25:42	00:25	25	61	Over 40
1:00:12	01:00	60	62	Over 40
0:51:05	00:51	51	63	Over 40
0:22:50	00:22	22	64	Over 40

I can make it work using Minutes column but I want to see visual in Hours & Minutes

[lbendlin · Accepted solution]

Yes.  Multiply the duration by 24, that will give you the hours with fractions.

[ppm1]

Please see this article for how to handle durations. It shows how to keep them stored as decimals (for aggregations, visuals, etc.) but display as you want with FORMAT as needed (e.g., in a tooltip).

Calculate and Format Durations in DAX – Hoosier BI

Pat

[lbendlin]

but I want to see visual in Hours & Minutes

How would that scale look like?  Hours and fractions, or hours and quarters?  You won't be able to show a mixed scale on the Y axis of a Column chart.

[littlepatos]

Would it be possible to show as decimals? Eg: 6 and a half hours is 6.5

[lbendlin · Accepted solution]

Yes.  Multiply the duration by 24, that will give you the hours with fractions.

[littlepatos]

@lbendlin Thanks - I feel I am close but not quite.

Do I do this by adding a new column with 24 then create a Measure to multiply by Duration by the New Column? 

[lbendlin · Accepted solution]

Depends on how you get the original duration. Remember that you cannot create columns from measures.

[littlepatos]

Thanks @lbendlin this worked 🙂