cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

Hi Acyrus1992, 

 

Sorry to bother you again. 

 

Will your expression work for averages? See my expression below. 

 

It just returns the total value not the average. Is something to do with DISTINCTCOUNT?

 

Average = DIVIDE(SUM('Time @ Depot'[TIME AT DEPOT]), DISTINCTCOUNT('Time @ Depot'[ENTERING DATE]))

Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

I see no issue with having an average included - but you need to apply the average at the sum level. 
Ie - calucalte the average before setting the Duration variable

If you get what I mean?

PS - was on holiday - so sorry about the delays

 

Highlighted
New Member

Re: Sum of duration of task with Days, Hours, Minutes

Hi all.

 

Hope it is ok that i reply in this thread.

 

I'm trying to use this code. and i've managed to make it show the duration/sum of the time from seconds to HHH:MM: SS

but the column is showing the sum for all rows in all rows.

 

Is there anyway that i can make it show the duration for every row and not a sum for all rows?

 

Thank you all in advance, you are helping alot!

 

Skärmklipp.PNG

Highlighted
New Member

Re: Sum of duration of task with Days, Hours, Minutes

Hi again. just noticed that once i started to filter, it actually changed.

sorry for that.

 

But now i'm having issues to get it to show average (can't understand what you explain above)

and also i want to add this to a column chart. and right now it only counts the measure and column as it is in text type.

but i can't change it to anything else as it will only result in error.

 

what am i missing?

 

thank you in advance.

Highlighted
New Member

Re: Sum of duration of task with Days, Hours, Minutes

Hi,
I'm working with time intervals in PowerBi. This topic was very useful for me but I need a little more help from you.

I have a table in the model (Orders) with #Order, StartTime, EndTime and ElapsedTime in seconds and I need to visualize the total elapsed time in format hhh:mm:ss (Elapsed Time is usually greather than 24 hours)

I did two measures to see the problem:
The first (Without Text) realize the calculations to express elapsed time in the new format, but I've trimmed the text formatting for testing purposes.

Without Text = VAR Duration = SUMX('Reportes de Operaciones';'Reportes de Operaciones'[ElapsedTime (s)])
VAR Hours = INT(Duration/3600)
VAR Minutes = INT(MOD(Duration;3600)/60)
VAR Seconds = MOD(Duration;60)
RETURN
CONCATENATE (Hours; CONCATENATE (Minutes;Seconds ))

The Second (Real Time), do the same calculations, but I've added format to display elapsed time in hhh:mm:ss

Tiempo Real = VAR Duration = SUMX('Reportes de Operaciones';'Reportes de Operaciones'[ElapsedTime (s)])
VAR Hours = INT(Duration/3600)
VAR Minutes = INT(MOD(Duration;3600)/60)
VAR Seconds = MOD(Duration;60)

VAR H =
IF ( LEN ( Hours ) = 1;
CONCATENATE ( "0"; Hours );
CONCATENATE ( ""; Hours )
)
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1;
CONCATENATE ( "0"; Minutes );
CONCATENATE ( ""; Minutes )
)
// Seconds with leading zeros
VAR S =
IF (
LEN ( Seconds ) = 1;
CONCATENATE ( "0"; Seconds );
CONCATENATE ( ""; Seconds )
)

VAR tH = IF(Hours=0;"";IF(Hours>1;Hours&"h: ";Hours&"h: "))
VAR tM = IF(Minutes=0;"";IF(Minutes>1;Minutes&"m: ";Minutes&"m: "))
VAR tS = IF(Seconds=0;"";IF(Seconds>1;Seconds&"s";Seconds&"s"))
RETURN
CONCATENATE (th; CONCATENATE ("";CONCATENATE (tm;CONCATENATE (""; CONCATENATE (ts;"" )) )))

Now..
In Table 1 you see that "Without text" measure works perectly but show unformatted data (obviously).
In Table 2 you see that "Tiempo Real" measure shows formatted data but.... I've lost de filter context.
PowerBiPowerBi
How do I to fix this? My brain burns...

 

 

 

 

 

 

 

 

Highlighted
Regular Visitor

Re: Sum of duration of task with Days, Hours, Minutes

Just want to add my thanks for this, hunted high & low and couldn't find a working solution, until now, Thanks!

Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

Hi mate

 

 

Is this still a problem for you?

 

 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors