cancel
Showing results for
Did you mean:
Highlighted
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
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

Re: Summarized columns value

Hi @Kolk1979,

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.
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.

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.