How to show average hours on graph

[abhishek_300]

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 · Accepted solution]

@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]

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]

@ShrikantKhanna

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

[ShrikantKhanna]

Okay. I will go ahead and do that.

[OwenAuger]

@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 ) )