Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Converting secs to hr

Please help. I am self teaching PowerBI and working on creating a report where I need to follow below formulas on PowerBI. 

 

Is there an easier way to do this on PowerBI? I have managed to change the seconds to hr:mm:ss but I need it to be like column K format so I can divide count of email to sum of chat. Thank you very much

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    I think you missed adding the second of 'hh:mm:ss' in your measure. You may create a calculated colum and modify measures as below. The pbix file is attached in the end.

    Calculated column:

    Second = SECOND([Time])

    Measure:

    Total Hours = 
DIVIDE(
    SUM('Table'[Hour])*3600+SUM('Table'[Minute])*60+SUM('Table'[Second]),
    3600,
    0
)
    Result = 
DIVIDE(
    SUM('Table'[Count of visitor emaiil]),
    [Total Hours],
    0
)

     

    Best Regards

    Allan

     

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

     

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create two measures as below.

    Total Hours = 
SUM('Table'[Hour])+
DIVIDE(
    SUM('Table'[Minute]),
    60,
    0
)
    Result = 
DIVIDE(
    SUM('Table'[Count of visitor emaiil]),
    [Total Hours],
    0
)

     

    Result:

     

    Best Regards

    Allan

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi v-alq-msft and amitchandak 

      Thank you very much for the responses. Sorry, I may have not explained it well but I need to convert hh:mm:ss into decimal. I did put too much information at first. 

       

      I currently have this formula on PowerBI and it is turning it into decimal but not correctly.   

      Chat duration decimal = HOUR(Webchats[Chat Duration]*3600)+MINUTE(Webchats[Chat Duration]*60)/3600 
       

      I have a column that is in hh:mm:ss and I need that be in decimal. 

       

      Really appreciate your time and help. Thank you!

      • v-alq-msft's avatar
        v-alq-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi, Anonymous 

         

        I think you missed adding the second of 'hh:mm:ss' in your measure. You may create a calculated colum and modify measures as below. The pbix file is attached in the end.

        Calculated column:

        Second = SECOND([Time])

        Measure:

        Total Hours = 
DIVIDE(
    SUM('Table'[Hour])*3600+SUM('Table'[Minute])*60+SUM('Table'[Second]),
    3600,
    0
)
        Result = 
DIVIDE(
    SUM('Table'[Count of visitor emaiil]),
    [Total Hours],
    0
)

         

        Best Regards

        Allan

         

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

         

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Calculations are not clear. But

    divide([c],3600.0) will give sec in hours

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.