Forum Discussion

abhishek_300's avatar
abhishek_300
Frequent Visitor
10 years ago
Solved

How to show average hours on graph

I have a table which have information on the issues that were worked upon by user. I want to show the information like issue open,issue closed count and want to show the average hour taken to resolve the issue. The report is a monthly report. So I have created a measure to calculate the average hour taken to close the issue. But when I drag this average hour taken in the lines values on the graph it shows the average in decimal point instead of the hh:mm:SS format.How can I show this average hours in hh:mm:SS format on the graph. Please advise
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    abhishek_300

     

    Are you looking for a measure as below?

     

    Total Hours = 
VAR HoursInDecimal =
    SUM ( 'Table'[Hours] )
RETURN
    FORMAT (
        TIME ( TRUNC ( HoursInDecimal ), 60
            * ( HoursInDecimal - TRUNC ( HoursInDecimal ) ), 0 ),
        "HH:MM"
    )

     

5 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    10 years ago

    abhishek_300

    One way to force Power BI to allow a time number format is to add TIME(0,0,0) to the existing measure.

     

    To ensure blanks are still blanks, you can do something like this:

    MeasureTimeFormat =
VAR OriginalMeasure = [OriginalMeasure]
RETURN
    IF ( NOT ( ISBLANK ( OriginalMeasure ) ), OriginalMeasure + TIME ( 0, 0, 0 ) )
  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    abhishek_300

     

    Are you looking for a measure as below?

     

    Total Hours = 
VAR HoursInDecimal =
    SUM ( 'Table'[Hours] )
RETURN
    FORMAT (
        TIME ( TRUNC ( HoursInDecimal ), 60
            * ( HoursInDecimal - TRUNC ( HoursInDecimal ) ), 0 ),
        "HH:MM"
    )

     

    • ShrikantKhanna's avatar
      ShrikantKhanna
      Icon for Helper II rankHelper II
      9 years ago

      Hi Eric_Zhang

       

      I tried your solution, however, it is not serving one purpose for me. If I am representing time for a particular day it is working fine, however, when I change the time range to full week it does not work.

      My requirement is to not only represent time on each row, I have to aggregate total duration for the selected time range as well (selected on slicers). 

       

      I, instead tried to break it down to Hours, minutes and seconds and then represent in text format to be able to serve my purpose.

       

      MEASURES - 

      CALLTIMES = SUM(DURATIONINSECONDS)

      HRS = TRUNC([CALLTIMES]/3600)

      MINS = TRUNC((([CALLTIMES]/3600)-[HRS])*60,0)

      SECS = TRUNC((((([CALLTIMES]/3600)-TRUNC([CALLTIMES]/3600))*60)-[MINS])*60)

      TIMING = IF(LEN([HRS])=1,"0"&[HRS],[HRS])&":"&IF(LEN([MINS])=1,"0"&[MINS],[MINS])&":"&IF(LEN([SECS])=1,"0"&[SECS],[SECS])

       

      I would not have to take a long route if there was formatting available in Power BI visuals, like we custom format in excel = [h]:mm:ss

       

      • Eric_Zhang's avatar
        Eric_Zhang
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        ShrikantKhanna

        Since this threads is old and already closed, please raise a new thread for your question. Thanks for your understanding. :)