Targets showing as time

[Anonymous]

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.

[PhilipTreacy]

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

[PhilipTreacy]

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]

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]

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