Calculation problem

[Anonymous]

Hey guys, i came across this problem with the calculation. Basically, Ive got different drive times and I need to calculate average drive time for each day, I've got the following(shown below). Is there any way to come up with solution so as that it would take my calculated duration for each different day and then it would only show the average for that day and that would be for every day?  Custom column is my calculated duration for each day, but I need for instance, if its Day 1, it would take all the driving times that day and take the average and would do the same for other days. 

[AlB]

Hi @Anonymous 

This can also be done in DAX but if you want it in Power Query, paste the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrCwMjKwMjBQ0lEyBGKlWB2gmImVqSm6mKmVoSWmOmNziJgRkpihAbqYsZWhKbqYkZWREbJYLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t, Day = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}, {"Day", Int64.Type}, {"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Average(Table.SelectRows(#"Changed Type",(inner)=>[Day]=inner[Day])[Time]))
in
    #"Added Custom"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[richbenmintz]

Hi @Anonymous,

Based on the file shared:

1. Convert the Time Column in Power Query to Type Time

2. Add a Custom Column in the 'Table' table caleed TimeInSeconds, formula below

=Duration.TotalSeconds([Time])

 3. Change Type of TimeInSeconds to whole number

4. Save the Query and return to the PBI editor

5. Create a new Table using the DAX Below

Avg By Day = SUMMARIZE('Table', 'Table'[Day], "avg_time", AVERAGE('Table'[TimeInSeconds]))

6. Add a new column to the 'Avg By Day' Table

Formatted Avg Time = 
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// There are 3,600 seconds in an hour
VAR Hours = INT ( 'Avg By Day'[avg_time] /3600)
// There are 60 seconds in a minute
VAR Minutes = INT ( MOD( 'Avg By Day'[avg_time] - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds = ROUNDUP(MOD ( MOD('Avg By Day'[avg_time] -++( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
RETURN
// We put the hours, minutes and seconds into the proper "place"
TIME(hours, minutes, Seconds)

8. Change Data Type to Time and set Format to suit your needs

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

@richbenmintz this is the link for the new pbix: https://gofile.io/d/eWJFJz Basically in power query you will see column Train, thats train number and I need to do the same thing, but if in day 1 there were three trains, I need to display it as 3. 

[richbenmintz]

Hi @Anonymous,

You can extend your base calc table query to return the count and distinct count of trains depending on the requirement, see below

Avg By Day = SUMMARIZE('Table', 'Table'[Day], "avg_time", AVERAGE('Table'[TimeInSeconds]), "train count", COUNTROWS('Table'), "distinct train count", DISTINCTCOUNT('Table'[Train]))

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

@richbenmintz  Indeed you are very helpful, this is another question that I got here: I need to round up time in the column below, like shown in the picture, to the closest hour. So if its lets say 15:30 it keeps and shows 15 hours but if it goes to lets say 15:40 then it would round it up to 16 hours. Any ways to achieve this? 

[richbenmintz]

Hi @Anonymous,

Rounded Formatted Avg Time = 
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// There are 3,600 seconds in an hour
VAR Hours = INT ( 'Avg By Day'[avg_time] /3600)
// There are 60 seconds in a minute
VAR Minutes = INT ( MOD( 'Avg By Day'[avg_time] - ( Hours * 3600 ),3600 ) / 60)
var roundedMin = if(Minutes > 30, 0, 30)
var roundedYear = if(Minutes > 30, Hours+1, Hours)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds = ROUNDUP(MOD ( MOD('Avg By Day'[avg_time] -++( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number


RETURN
// We put the hours, minutes and seconds into the proper "place"

TIME(roundedYear, roundedMin, Seconds)

see results below

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

@richbenmintz  Thats actually very helpful, now I need to add a column that would just count the train number per each day, how can I do that in the same format?
Many many thanks,

Gediminas. 

[amitchandak]

@Anonymous , You have try a measure like

sumx(values(Table[Date]),[Average duration])

for that you need to have column like
Date = [opdate6].date 

[richbenmintz]

Hi @Anonymous,

Could you provide your sample data as a table as opposed to an image?

Thanks,

[Anonymous]

@richbenmintz  Hey, 
you will find file here: https://gofile.io/d/uA8DHE. Open the power query and the first table called "Table" contains few different days and time for each day. What I need is to create another column that will always return me the average time for that day. So if theres 4 different times for day 1, then it should be just one average time in that column. 

Many many thanks.