Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abhishek_300
Frequent Visitor

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
1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee

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

Capture.PNG

 

View solution in original post

5 REPLIES 5
Eric_Zhang
Employee
Employee

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

Capture.PNG

 

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

 

@ShrikantKhanna

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

Okay. I will go ahead and do that.

OwenAuger
Super User
Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.