Showing average duration in bar chart

[LeoQ]

Hi everybody!

I'm stuck with this issue:

I'm working with a dataset from a ticketing system. In the file I attach here (Test), I have one column of sectors and another of time that passed bewteen the start and end of the ticket (Duration).

However, when I export this dataset to Power BI and try to visualize the average duration for each sector, the duration is shown in decimals (Image1). What can I do to show the average in the format (HH:MM:SS).

The formula I used to calculate the Average duration was Average_Duration = AVERAGE(BASE_FINAL[Duration])

I also tried with  Average_Duration = FORMAT(AVERAGE(BASE_FINAL[Duration]), "HH:MM:SS") but the output is a string.

I cannot attach files, so I paste photos:

Test

Image 1

[Anonymous]

Hi  @LeoQ ,

Currently the power bi desktop does not support the display duration of the Y axis, but you can try to create a metric for calculation by using the following Dax:

Duration_measure =
VAR Duration = [Average_Duration]
VAR Hours =INT(Duration/3600)
VAR Minutes =INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
VAR Seconds =ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)
VAR H =
    IF ( LEN ( Hours ) = 1,
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )

VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )

RETURN
CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )

This is a link to related content, I hope it will help you:

https://community.powerbi.com/t5/Desktop/Display-duration-as-HH-MM-SS-on-Y-axis/td-p/87527

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LeoQ]

Thank you very much! I tried this one, but it did not let  me use this measure as values for the chart.

[amitchandak · Accepted solution]

@LeoQ , refer if these two can help

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

[sraj]

Hi - I have a question on Chelsie's duration measure link you have, what if I don't want it to roundup or down?  Would like to just show the value as it is.

Chelsie Eiden's Duration AppWaittime =

// Duration formatting

// * @konstatinos 1/25/2016

// * Given a number of seconds, returns a format of "hh:mm:ss"

//

// We start with a duration in number of seconds

VAR Duration = AVERAGE([# of Mins APPTWAITtime])

// 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

RETURN

// We put the hours, minutes and seconds into the proper "place"

Hours * 10000 + Minutes * 100 + Seconds

[LeoQ]

Thanks a lot! The first link solution worked perfectly!