Forum Discussion

LeoQ's avatar
LeoQ
Icon for Helper I rankHelper I
5 years ago
Solved

Showing average duration in bar chart

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

Need Help

5 Replies

    • LeoQ's avatar
      LeoQ
      Icon for Helper I rankHelper I
      5 years ago

      Thanks a lot! The first link solution worked perfectly!

       

    • sraj's avatar
      sraj
      Icon for Responsive Resident rankResponsive Resident
      3 years ago

      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
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      LeoQ
      Icon for Helper I rankHelper I
      5 years ago

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