Transform seconds to hh:mm:ss format using measures & calculated columns

[athan]

Hi all!,

i am new to PowerBI and struggling to show time (seconds) aggredated data on charts in hh:mm:ss format.

Have a table [table1] with a field table1[time] which stores time (duration actually) in seconds.

Have created a calculated column [time-duration] by following this well known post: http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 however this does not work for visualizations that perform calculations (aggregation) on [time] column.

Column [num-incidents] is the number of records (incidents) that have the same duration on a specific date. So to calculate the AVG of duration i have created a custom column inside the query editor:

[time-incidents-prod] = [time]*[num-incidents]

I am performing the aggregation on my report using a measure:

DSS-TIME-AVG-AGR = ( SUM(table1[time-incidents-prod]) / SUM(table[time]) )

That gives me the average of duration of incidents.

Then i have manage to tansform it, into hh:mm:ss format using a second measure:

DSS-TIME-AVG-AGR-TIMED = FORMAT(table1[DSS-TAT-AVG-AGR]/ 60 / 60 /24,"HH:MM:SS")

I can display it into visualizations like cards etc. but not able to use it into timeline charts to label Y axis:

a

Any ideas on if this is possible?

As alot of people have already said, PowerBI should support a 'duration' data type that could be used to perform calculations etc and will support different format types.

Thanks!

[Greg_Deckler]

Unfortunately I still do not think there is a true solution to this yet. 😞