DAX formula to convert time HH:MM:SS into decimal time mm.ss

[IwannaBeLikeU]

Call center data - due to the number of records, I'm importing call data into Access and setting the time field type to "date with time" and displaying hh:mm:ss. When I import this data into PowerPivot for Excel consumption, I create a pivot table and try to average the time data, but I get an error message due to the type of data. So now I'm thinking I'll convert the HH:MM:SS to seconds, but I'm having an issue with the DAX formula.
"=HOUR('Contact_History 18'[Handle Time])*3600+minute('Contact_History 18'[Handle Time]*60)+Second('Contact_History 18'[Handle Time])"

For 00:08:10 I get a result of 20.00 (That should say 490 seconds)

Any help on this matter would be greatly appreciated.

 

[IwannaBeLikeU]

The title is wrong and should read just seconds, not mm.ss!

 

[jdellasala]

In Excel, the value of a displayed time of 00:08:10 which LOOKS like 8 minutes 10 seconds using the format hh:mm:ss, is shown in the Formula Bar as 12:08:10 AM, and has an actual value of 0.005671296 with the General format.
When brought into Power Query, it has to be defined as a Duration type to display as 0.00:08:10. When that's brought into the Data Model, there is no format available for Duration. The only data values available are Text, Date, Decimal Number, Whole Number, and Currency (Decimal Number with only two decimal places).
Your equation would work fine in Excel

=HOUR([@[Handle Time]])*3600+MINUTE([@[Handle Time]])*60+SECOND([@[Handle Time]])

, as well as in Power Query

= Table.AddColumn(#"Changed Type", "PQ Seconds", each Duration.Hours([Handle Time] ) * 3600 + Duration.Minutes([Handle Time])*60 + Duration.Seconds( [Handle Time] ))

but simply cannot be done in the Data Model.
I'd like to see the Power Pivot toolbar above with a cell in Handle Time selected to see what it's determining it is.

 

[IwannaBeLikeU]

 

[IwannaBeLikeU]

In Excel, the value of a displayed time of 00:08:10 which LOOKS like 8 minutes 10 seconds using the format hh:mm:ss, is shown in the Formula Bar as 12:08:10 AM, and has an actual value of 0.005671296 with the General format.
When brought into Power Query, it has to be defined as a Duration type to display as 0.00:08:10. When that's brought into the Data Model, there is no format available for Duration. The only data values available are Text, Date, Decimal Number, Whole Number, and Currency (Decimal Number with only two decimal places).
Your equation would work fine in Excel

=HOUR([@[Handle Time]])*3600+MINUTE([@[Handle Time]])*60+SECOND([@[Handle Time]])

, as well as in Power Query

= Table.AddColumn(#"Changed Type", "PQ Seconds", each Duration.Hours([Handle Time] ) * 3600 + Duration.Minutes([Handle Time])*60 + Duration.Seconds( [Handle Time] ))

but simply cannot be done in the Data Model.
I'd like to see the Power Pivot toolbar above with a cell in Handle Time selected to see what it's determining it is.

 

[jdellasala]

View attachment 81443

Saw the first post.
What is the source of the data?

 

[IwannaBeLikeU]

Saw the first post.
What is the source of the data?

The data comes from an SQL BD, but I can only export that one week at a time into excel (400k rows of data). That gets compiled into access so I can get YTD records, and then I connect PowerPivot to that dB.
I don't connect directly to the SQL source as it only retains 30 days of data on a rolling basis.

 

[jdellasala]

The data comes from an SQL BD, but I can only export that one week at a time into excel (400k rows of data). That gets compiled into access so I can get YTD records, and then I connect PowerPivot to that dB.
I don't connect directly to the SQL source as it only retains 30 days of data on a rolling basis.

I'm at a loss. I have the same data, and the DAX formula

=HOUR(ContHist[Custom])*3600+MINUTE(ContHist[Custom])*60+SECOND(ContHist[Custom])

works for me! The formula is for Calculated Column 1.

 

[IwannaBeLikeU]

I'm at a loss. I have the same data, and the DAX formula

=HOUR(ContHist[Custom])*3600+MINUTE(ContHist[Custom])*60+SECOND(ContHist[Custom])

works for me! The formula is for Calculated Column 1.

View attachment 81448

What is the format you have for Calculated Column 1?

 

[jdellasala]

What is the format you have for Calculated Column 1?

Whole Number - General. I didn't set that. In fact it's Auto (Whole Number).