Summarize column of datatype time

[Nazeem81]

I have problems summarizing the values in a column (inloggad tid) showing time in format h:mm:ss, the total value is not coming correct.

I tried creating to new measures: 

Inloggad sum = MAXX(DISTINCT(Tiddata[ProjektDatumSäljare]);MAX(Tiddata[Inloggad tid]))

Inloggad tid sum = SUMX(DISTINCT(Tiddata[ProjektDatumSäljare]);[Inloggad sum])

I tried to use the new measure Inloggad tid sum but it s still not summarizing correctly. The measure Inloggad tid sum is of format Date Time.

When I tried this in Power Query in Excel it was working. When I created a pivot table and used the measure "Inloggad tid sum" I just changed the format of the values to [t]:mm:ss and the summary came correct.

How can this be solved in Power BI Desktop?

[Nazeem81]

When I use code below the summary is showing correct value, for example 34days02:59:27. But I dont want to show the summary in days, I want to show total hours, minutes and seconds. Instead of  34days02:59:27 it should show 818:59:27

Anyone who can help me solve this?

newDuration =

VAR TotalSeconds=SUMX('Tiddata';HOUR('Tiddata'[CopyDuration])*3600+MINUTE('Tiddata'[CopyDuration])*60+SECOND('Tiddata'[CopyDuration]))

VAR Days =TRUNC(TotalSeconds/3600/24)

VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)

VAR Mins =TRUNC(MOD(TotalSeconds;3600)/60)

VAR Secs = MOD(TotalSeconds;60)

return IF(DAYS=0;"";IF(DAYS>1;DAYS&"days";Days&"day"))&IF(Hors<10;"0"&Hors;Hors)&":"&IF(Mins<10;"0"&Mins;Mins)&":"&IF(Secs<10;"0"&Secs;Secs)

[v-yuezhe-msft · Accepted solution]

@Nazeem81,

Create the following measures in your table.

totalseconds= SUMX('Tiddata',HOUR('Tiddata'[CopyDuration])*3600+MINUTE('Tiddata'[CopyDuration])*60+SECOND('Tiddata'[CopyDuration]))

Measure = RIGHT ( "0" & INT ( [totalseconds] / 3600 ), 2 )
& ":"
& RIGHT (
"0"
& INT ( ( [totalseconds] - INT ([totalseconds] / 3600 ) * 3600 ) / 60 ),
2
)
& ":"
& RIGHT ( "0" & MOD ([totalseconds] , 3600 ), 2 )

Regards,
Lydia

[Nazeem81]

Anyone, please?

[Greg_Deckler]

Can you share sample data so that I can try to recreate this?

[Nazeem81]

Hi,

See link below dfor sample data. I Want to be able to summarize inloggad tid in Power BI deskptop. Picture below shows its not working for me to summarize when creating a new measure to be able to sum.

https://ufile.io/9rio2