Return the Average Start Time by Route Identifier and Ignore Blanks

[clarkpaul]

The source data has duplicate Route Identifiers and duplicate Route Start Times.  I'm trying to find the
average Route Start Time without duplicates and ignore blank values.
Below is a sample of the data:

Any help would be appreciated!

[clarkpaul]

Perfect, thank you!

[Anonymous · Accepted solution]

Hi @clarkpaul ,

Create a calculated column to convert the time to seconds.

_seconds = HOUR('Table'[Start Times])*3600+MINUTE('Table'[Start Times])*60+SECOND('Table'[Start Times])

Then create a measure like below.

Measure = 
var _avg = SUMX(ALL('Table'),'Table'[_seconds])/CALCULATE(COUNT('Table'[Identifiers]),'Table'[Start Times]<>BLANK())
return
VAR hours =
    ROUNDDOWN ( _avg / 3600, 0 )
VAR minutes =
    ROUNDDOWN ( MOD ( _avg, 3600 ) / 60, 0 )
VAR seconds =
    INT ( MOD ( _avg, 60 ) )
VAR milliseconds =
    round(MOD ( _avg, 1 ) * 100,0)
RETURN
    FORMAT(hours,"00") & ":"
        & FORMAT(minutes, "00")
        & ":"
        & FORMAT(seconds, "00")

 

Best Regards,

Jay