Calculate Sum of HH:MM column as text data type

[etabaku14]

Hi,
I want to calculate the sum of a column that contains data as text in the following format: 'HH:MM'

Time

04:15

02:15

11:05

17:35

I have all the listed times in a column, and want to add a Total value that adds all those time. Tried some formulas which were posted here at the community but couldn't find one where the data type of 'Time' column was text.

[v-zhangti · Accepted solution]

Hi, @etabaku14 

You can try converting text to time format here.

Or try using the Timevalue function to change the time in text format to time format.

Column = TIMEVALUE([Time])

TIMEVALUE function (DAX) - DAX | Microsoft Learn

Measure = SUM('Table'[Time])

Measure 2 = CALCULATE(SUM('Table 2'[Time]),ALL('Table 2'))

If the result is different from what you expect, please provide more details.

Best Regards,

Community Support Team _Charlotte

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

[etabaku14]

Thanks for your answer.
I have one question about: 

Measure 2 = CALCULATE(SUM('Table 2'[Time]),ALL('Table 2'))

Since I have filters in my report (Date range, radio buttons etc) this 'ALL' doesn't calculate the sum of the filtered data but all data with no filters.
Is there any option to calcualte based on the date range i picked, or let's say based on the filtered table

[v-zhangti]

Hi, @etabaku14 

You can try.

Sample data:

Measure = CALCULATE(SUM('Table'[Time]),FILTER(ALL('Table'),[Date]=SELECTEDVALUE('Table'[Date])))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[amitchandak]

@etabaku14 , Try like

time(0,0,0) + Sumx(Table, left([time],2)*1 + right([time],2)/60) /24

[etabaku14]

Thank you for your time, but this didn't calculate the right value for the sum. I fixed this problem by creating a new column with a decimal number as the data type based on the column 'HH: MM'. Used formulas to express the time in a decimal number.

Now I have another problem: The calculated sum is not correct of the column. Basically I have a column with decimal numbers, but when I add the summarize option a wrong total is displayed.
Example:
I have a table same as above:

Time

1.2

2.5

2.3

And want to add a column to another table with the correct total value.

Total Value

6.0

But the Total Value column display wrong total
What I have tried:
SUMX(table, table[Time])
SUM, CALCULATE, COUNT ...

I am still not finding why the sum is different 😕