Time in histogram

[Anonymous]

Hey guys,

I've been working on this for a few weeks now and still cant work out a solution. So basically, I need to display the duration as a value in the histogram. Shown in the image below. I tried converting it to text, using different kind of calcutions with DAX, but I can never get it to values field and display it on data labels like that.  The closest I got to is like this below, duration shown in the X axis.  My whole idea is to display time it took to get to stop and if its above set limit(say 4 hours) then its shown as red, over time. Any ideas how can I do that ? That would be much much appreciated. 

[Anonymous]

I use the following code and get the time format in format like 3,24(3:24). howerver, instead of comma i would like to use - or just plain space, however I would still need to be a able to display it as a value and aggregate it, any ideas?

Test2 = // We start with a duration in number of seconds VAR Duration = DATEDIFF( 'Klaipėda - Radviliškis'[opdata], 'Klaipėda - Radviliškis'[Added Index1.opdata], SECOND ) // There are 3,600 seconds in an hour VAR Hours = INT ( Duration / 3600) // There are 60 seconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number // These intermediate variables ensure that we have leading zero's concatenated onto single digits // Hours with leading zeros VAR H = IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) ) // Minutes with leading zeros VAR M = IF ( LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ) ) // Seconds with leading zeros VAR S = IF ( LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ) ) // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" RETURN CONCATENATE ( H, CONCATENATE ( ",", CONCATENATE ( M, S) ) )

[negi007]

Create below columns in your data

ACH%COL = TIME(23,59,59)

ACH% = Activity[Time]/Activity[ACH%COL]

 

once you have created above two columns then using bar chart and matrix visual you can create below visual

[Anonymous]

@negi007 

Could you share your pbix file with me please? Would appreciate it

[negi007]

@Anonymous 

Here is the link

PBIX Link 

[Anonymous]

So I used the code konstatinous found, but instead ttry to use comma and everything works out fine until I get 5,1 but in this case i would like to display 5,10 as an actual time, any way i can add an extra zero?

[VijayP]

@Anonymous 

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 

I think you can get your solution from this post in Detailed way.

Hope this is a solution for you. Please share kudos 

Regards

Vijay Perepa

[Anonymous]

This may seem like a solution, however it gives time as a text, where you still cant aggregate it.