Convert measure value into hh:mm:ss

[sabeensp]

Hello,

I have a meassure which is SUM(Table1[Field1]) + SUM(Table2[Field2]) Returns a single number like 7685

I need to convert 7685 to HH:MM:SS, how do I do that?

Thanks in advance.

[v-cherch-msft]

Hi @sabeensp 

You may create a measure like below and format it.

Measure = TIME(0,0,SUM(Table1[field1])+SUM(Table1[field2]))

Regards,

[sabeensp]

@v-cherch-msft  I tried

Production Time = TIME(0,0,SUM(TABLE1[Column1]) + SUM(TABLE2[Column2]))

Both Columns are numeric, they are in different related table, but that should not cause the problem.

I get following error:

Error Message:
MdxScript(Model) (15, 45) Calculation error in measure [Production Time]:

An argument of function 'TIME' has the wrong data type or the result is too large or too
small.

[v-cherch-msft · Accepted solution]

Hi @sabeensp 

You may try below measure.

Measure =
TIME ( 0, 0, 0 )
    + DIVIDE ( SUM ( TABLE1[Column1] ) + SUM ( TABLE2[Column2] ), ( 24 * 60 * 60 ) )

Regards,

[adminbimimed]

It worked almost perfectly for me, except that... 🙂

I'm working with call durations in a call center, so it's basically only mm:ss. If I format hh:mm:ss, it shows 00:02:48, but if I format as mm:ss, it shows 12:48 (?!)... I'm guessing that when it is hh:mm:ss, it understand m is for minutes, but if it's formatted mm:ss, it understands m as for month... But I don't know how to solve it... Help! 🙂

[RolandsP]

Does 7685 represent seconds?

[sabeensp]

@RolandsP  Yes, there are seconds