Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Summarized columns value

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?

  • Anonymous's avatar
    Anonymous
    7 years ago

    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.
     
     
     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable

      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.