Forum Discussion
Displaying time (hh:mm:ss) on a chart
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):
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
6 Replies
Jimmy801Community Champion
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
JimmyJimmy 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
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
Microsoft Employee
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
