Displaying time (hh:mm:ss) on a chart

[joaoga1]

Hi everyone, 

I'm currently working on a Power BI project and I ran into a problem. I want a bar chart, with different subjects, and for the bars I want a sum of hours worked on each subject (example: I worked 03:30:00 hours on maintenance and a friend worked 01:00:00 hours on maintenance too, so the bar must show a total of 4:30:00 [hh:mm:ss] for the maintenance subject), taking into consideration that I have different types of subjects, how can I do that?

Here's an example of what I need (on excel):

And here's what I managed to do (though it's not what I want):

[Jimmy801 · Accepted solution]

Hello @joaoga1 

you can create a new measure that gives you a text format as you need it. But as I think it's not possible to add this as label text, but only as tooltip. Here how the measure could look like (not tested)

Measure = sumx('table', hour('table'[time])) + TRUNC(sumx('table', MINUTE('table'[time]))/60)  & ":" & mod(sumx('table', MINUTE('table'[time])), 60) & ":00"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[mahoneypat]

I don't think you can do exactly that.  A couple things to consider

1. you could make your chart with a hours value in decimal form as suggested, and then have a second column/measure with the desired format that you use as a Tooltip

2. You could do the same as #1 but make a custom bar chart with Charticulator to add a text label that shows your time in desired format permanently (not a hover tooltip).

Can You Say "Charticulator"? - I Knew You Could - Portland Power BI UG - YouTube

Pat

[Jimmy801]

Hello @joaoga1 

Datetime-formats cannot be summed. You have to create a measure like this

TimeValue = sumx('Table', hour('Table'[Hours])) +(sumx('table', MINUTE('Table'[Hours]))/60)  +  +(sumx('table', SECOND('Table'[Hours]))/60/60)

This gives you a digital version of your datetime like 19,5 so you can put it in the gannt. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[joaoga1]

Jimmy thanks for the answer, it really helped!

Altough, I need to visualize the amount of hours in the format of (hh:mm:ss) on the chart, is there any way I can do that?

[Jimmy801 · Accepted solution]

Hello @joaoga1 

you can create a new measure that gives you a text format as you need it. But as I think it's not possible to add this as label text, but only as tooltip. Here how the measure could look like (not tested)

Measure = sumx('table', hour('table'[time])) + TRUNC(sumx('table', MINUTE('table'[time]))/60)  & ":" & mod(sumx('table', MINUTE('table'[time])), 60) & ":00"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[joaoga1]

Thanks again Jimmy, but how do I add it as a tooltip?

[v-yingjl · Accepted solution]

Hi @joaoga1 ,

Just put the measure in the Tooltip field:

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