Calculate and display duration in HH:MM:SS format (09:59:00) in power bi KPI and other chart visual

[MayurUshir]

I have filed with Text Data type (08:30) which is duration (HH:MM)

I want to calculate average of that duration and display in duration format, please refer below example:

ActualTime Filed :Text Data type (08:30) which is duration (HH:MM)

formula:

Average(ActualTime)

Display 

(08:30) which is duration (HH:MM)

I have alreday use duration data type format and i will get 08:30:00:00 not get converted properly.

[Anonymous · Accepted solution]

Hi @MayurUshir 

I think your HH:MM column is text format. Try this measure.

Avg HH:MM =
VAR _T =
    ADDCOLUMNS ( 'Table', "TimeValue", TIMEVALUE ( 'Table'[HH:MM] ) )
VAR _T2 =
    ADDCOLUMNS ( _T, "HH", HOUR ( [TimeValue] ), "MM", MINUTE ( [TimeValue] ) )
VAR _AllMinutes =
    SUMX ( _T2, [HH] ) * 60
        + SUMX ( _T2, [MM] )
VAR _Count =
    COUNTROWS ( 'Table' )
VAR _AVGMinutes =
    DIVIDE ( _AllMinutes, _Count )
VAR _HH_MM =
    COMBINEVALUES (
        ":",
        FORMAT ( INT ( DIVIDE ( _AVGMinutes, 60 ) ), "0#" ),
        MOD ( _AVGMinutes, 60 )
    )
RETURN
    _HH_MM

My Sample:

Result is as below.

Best Regards,
Rico Zhou

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

[mahoneypat]

Please see this video for best practices on calculating and formatting durations.

(5) It's About Time - Part Two - YouTube

Pat

[Greg_Deckler]

@MayurUshir You need Chelsie Eiden's Duration: Chelsie Eiden's Duration - Microsoft Power BI Community