Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Targets showing as time

Hiya,

 

I am hoping some can point me in the right direction.

 

I am trying to convert into time format based on the total number completed/target

 

In excel I have used sum(Total_completed/target)/24 and then changed the format of the cell as time and it has worked. However I am struggling to get the same result in Power BI.

 

At the moment I have 2 measures [total Manual#]/[Manual Target]

 

Thanks a lot.

Need Help

4 Replies

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

    Hi Anonymous 

    Please supply some sample data and what you want the result to look like.

    In PBI there is no Time data type, so you can end up storing time/durations as numbers indicating the total number of minutes or hours and then converting those to some human readable format like hh:mm:ss.  To do this requires some DAX code like this

    HMS = 

VAR _time = SELECTEDVALUE('Table'[Minutes])

VAR _hrs = _time / 60 //(Elapsed_Time - days) * 24

VAR hrs = INT(_hrs) 

VAR mins = ROUND((_hrs - hrs) * 60, 0)

RETURN

FORMAT(hrs,"00") & ":" & FORMAT(mins,"00") & ":00 "

    Which will convert minutes like this

    If you can post some data I'll be happy to help.

    regards

    Phil

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Philip,

       

      Thanks for coming back to me, apologies for not replying sooner.

       

      The sample data looks like this.

       

      Main Data

       

      Target

       

       

      Summary

       

       

      Thanks again for your help.

       

      Claire

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Philip,

         

        Apologies I also have a measure which is a sum in HH:MM:SS time they had to to the work.

         

        Thanks,

        Claire

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

    Hi Anonymous 

    Can you supply the sample data as a file or at least copy/paste a table so I don't have to manually type everything in again?

    Please explain what you want to do with the Main Data table.  Am I supposed to be counting something here?  Is this where the Total Completed comes from?

    Thanks

    Phil