Changing decimal number into duration and display in visual chart

[LinlookZDJ]

Dear Power BI Experts,

I have the following "Time Gap" column display the duration in decimal format. I have took quite some time but couldn't figure out the solution.

How can I display the decimal number in time duration and display in visual chart? For example for 0.002777 it should display as 4mins in the visual chart.

Attached my sample dashboard for your reference:

https://drive.google.com/drive/folders/1Lkea9FOpyR8p6GNlxKpEM__j_Lz0X5kS?usp=sharing

Thank you for your time.

Best regards

Lin  

[mahoneypat · Accepted solution]

You can leave your column as a decimal and use a measure expression like this in your tooltip to see the average formatted in hh:mm:ss.

Avg Time Gap = FORMAT(CONVERT(AVERAGE(Testing[Time Gap]),DATETIME), "hh:mm:ss")

Pat

[LinlookZDJ]

Hi Pat,

Thanks for the solution! It works!

Just that is it possible for the tooltip value to appear on my bar instead of pointing on the bar to see the duration? For example 25:36 to swap with 0.02. So that we can look at the bar directly instead of pointing on it. 

Appreciate a lot for your kind assistance!

Best regards

Lin

[Anonymous]

Even easier:

= Table.AddColumn(LastStep, "Minutes", each Duration.TotalMinutes(Duration.From([Time Gap])))

--Nate

[Anonymous]

You can add a column--let's say your last step name is LastStep. Add a new step, and in the formula bar, type:

= let day = each [Time Gap] in Table.AddColumn(LastStep, "Minutes", each Duration.TotalMinutes(#duration(day, 0,0,0)))

--Nate

[Greg_Deckler]

@LinlookZDJ Couple of ways to approach this. You could convert this to minutes by doing the following:

INT([Time Gap] * 24 * 60)

You could also convert this to seconds:

INT([Time Gap] * 24 * 60 * 60)

and then use a custom format string ala Chelsie Eiden's Duration - Microsoft Power BI Community

[LinlookZDJ]

Hi Greg,

Thanks for the prompt reply.

However, I have tried to use your DAX formula to create a measure but it was unable to detect my Time Gap column, is there anything I have missed out?

Best regards

Zhang DunJie

[Greg_Deckler]

@LinlookZDJ Well if it is a measure, then you will need an aggregation like SUM, MAX or AVERAGE around your column reference.