Summarized columns value

[Kolk1979]

I have a column timespent_inSeconds. Which is a Text field with a value. I have created a column Minuten (minutes) with the following Query:

Minuten =

VAR TimeSecs = ( IncidentTimeRegistrations[timeSpent_inSeconds] )

VAR TimeMins =

   DIVIDE ( TimeSecs; 60 )

VAR JustSeconds =

   TimeMins - TRUNC ( TimeMins )

VAR JustMinutes =

   TimeMins - JustSeconds

VAR Restminutes = JustMinutes - (IncidentTimeRegistrations[Uren]*60)

RETURN VALUE ( Restminutes )

This is working

I also created a column uren (hours) with the following query

Uren =

VAR TimeSecs = ( IncidentTimeRegistrations[timeSpent_inSeconds] )

VAR TimeHours =

   DIVIDE ( TimeSecs; 3600 )

VAR JustSeconds =

   TimeHours - TRUNC ( TimeHours )

VAR JustHours =

   TimeHours - JustSeconds  

RETURN VALUE ( JustHours )

This is also Working. So far so good.

When I go to my report and summarize the hours and minutes it goes wrong. It indeed summarize all of the values but in the minutes table it says for example 240 minutes. What I want to accomplish is that these 4 hours (240 minutes) will be added to the hour table and removed from the minutes. Everything below 60 minutes should stay in the minutes table.

For example: if I have 257 minutes in the minutes table and 2 in the hours table, it should be 6 hours and 17 minutes.

Does anyone know how to accomplish this?

[Kolk1979]

Now    
Ticket Number   Short Description            Engineer            Hours           Minutes
xx                        Blabla                              Me                     1                   95
    
Should be    
Ticket Number   Short Description            Engineer            Hours           Minutes
xx                        Blabla                              Me                     2                   35
    
Hours and Minutes are summaries of different lines which have the same ticket number.

[Kolk1979 · Accepted solution]

I found the solution:

i created a new column and put in the following dax formula:

Time = FORMAT((IncidentTimeRegistrations[timeSpent_inSeconds]/3600)/24; "HH:mm:ss")

pointing to the timespent which is formatted as a number. This new Time Column is formatted as a time column in the notation HH:mm:ss

After that i create a new measure based on this column with the following DAX formula:

Meting = SUM(IncidentTimeRegistrations[Time])

i formatted this measure as date/time with notation HH:mm. I don't need the seconds.

[v-yulgu-msft]

Hi @Kolk1979,

Please refer to this link to provide sample data and show your desired output.

How to Get Your Question Answered Quickly

Regards,

Yuliana Gu