cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kolk1979 Frequent Visitor
Frequent Visitor

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Kolk1979 Frequent Visitor
Frequent Visitor

Re: Summarized columns value

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 3
Community Support Team
Community Support Team

Re: Summarized columns value

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Kolk1979 Frequent Visitor
Frequent Visitor

Re: Summarized columns value

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 Frequent Visitor
Frequent Visitor

Re: Summarized columns value

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.