Forum Discussion

Mgrstrg's avatar
Mgrstrg
New Member
5 years ago

How to convert Time column to seconds

Hello,

I'm new to power bi and I have a column that already has time as hh:mm:ss but I need to get the average and everytime I try it only gives me the option of earliest or latest. I've tried a measure =average('table[column]) but it doesn't work.

 

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    hi Mgrstrg 

     

    Usually Time Value cannot be Averaged out since it is not a mathematical expression. Though there is an alternate way to find the mid value of different Time. 

    1. Convert Time into Integer. It can be done using

    TimeInInt = HOUR(Table1[Time])*3600+MINUTE(Table1[Time]*60)+SECOND(Table1[Time])

    2. Take Average of this number. It can be done using

    TimeAvg =  CALCULATE(Average(TimeInInt))

    3. Convert this Integer back to Time. Please refer below Article for exact calculation

     

    Solved: Convert An Int Field to Time Format - Microsoft Power BI Community

     

    Please let me know if this works!

     

    Thanks!!!

     

    Kindly accept the solution if you agree 🙂 and would love to have Kudos if you like!!!

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You can very easily do it:

    [Measure] =
CONVERT(
    AVERAGEX(
        'Table',
        1 * 'Table'[Column]
    ),
    datetime
)