Group By time 15 minute colum and total Measure

[Anonymous]

Hi,

i have a table that displays this data,

14:30:00	166
14:45:00	93
15:00:00	126
15:15:00	3299
15:30:00	7184
15:45:00	5957
16:00:00	5026
16:15:00	4793
16:30:00	4046
16:45:00	2418
17:00:00	2794
17:15:00	3072
17:30:00	4942
17:45:00	4660
18:00:00	3391
18:15:00	4557
18:30:00	3957
18:45:00	3398
19:00:00	4066
19:15:00	7676
19:30:00	8174
19:45:00	9586
20:00:00	9189
20:15:00	8089
20:30:00	7354
20:45:00	7174
21:00:00	4867
21:15:00	11124
21:30:00	12479
21:45:00	12287
22:00:00	12587
22:15:00	13006
22:30:00	12815
22:45:00	11781
23:00:00	6028
23:15:00	9619
23:30:00	11003
23:45:00	9945
00:00:00	10136
00:15:00	10141
00:30:00	7644
00:45:00	7262
01:00:00	6953
01:15:00	7880
01:30:00	8913
01:45:00	8236
02:00:00	8191
02:15:00	8395
02:30:00	7446
02:45:00	8073
03:00:00	8344
03:15:00	5173
03:30:00	3387
03:45:00	4297
04:00:00	5998
04:15:00	5988
04:30:00	5336
04:45:00	2710
05:00:00	823
05:15:00	507
05:30:00	180

The time data is created by extacting 15 minutes breakdown from my time and date colum colum,

Time.From(Number.RoundDown(96*Number.From(Time.From([CDATE]))/1)/96)

and then i add a custum measure that i put into values.

CoyTotal = calculate (countrows(ItemTracking), search("R***C-Normal*", ItemTracking[Merged],, 0)>0) + calculate (countrows(ItemTracking), search("LD***-Normal", ItemTracking[Merged],, 0)>0) + calculate (countrows(ItemTracking), search("SPUD***-Normal*", ItemTracking[Merged],, 0)>0) + calculate (countrows(ItemTracking), search("SVC***-Normal", ItemTracking[Merged],, 0)>0) + calculate (countrows(ItemTracking), search("R*5*C-MAX_R*", ItemTracking[Merged],, 0)>0)

I have tried to group by, but it dosent group based on time... would there be a way of creating a custum colum that would display this data?

[Greg_Deckler]

I am not clear on what you are trying to achieve, can you post the end result that you want? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

This data displays on my front-end as a matrix,

Rows is based on of 15 minute column  and the values is my total pieces

15 minutes breakdown by using time from.

Time.From(Number.RoundDown(96*Number.From(Time.From([CDATE]))/1)/96)

What i would like is the same data, but in a custom column because I need to create some variable Dax Scripts that would add specific rows up to give me a total. I will need add rows for example 1-4 then 2 -5 once i have created this column.

So the end result is the same as what you can see above, but i would need this in a new table or two custom column's that would display the time and totals like the matrix table above.

[Anonymous]

Please take a look at the attached pbix file below and fire up Power Query and step through the applied steps:

1. Grab the minute value from your time column
2. Using that column with just the Minutes, add a column to see where it shoud go. I.e. if its the 19th minute it should go into the 30th minute "bucket".  There's a parameter in there to change the range, but defaulted to 15 minutes. 
3. Using that new minute value, grab the Hour from the original Time and and merge with the new minutes column.  This will be the Time bucket end
4. Create a new column which will be the time bucket start, which is just the value from #3 - the Parameter ( in this case 15 minutes)
5. Merge thse two columns, and call it Time Range. Remove all intermediate columns no longer needed
6. Group the rows by this Time range, and Sum the Amount (or whatever) column 

Here's the full Power Query code. Though the last part ( grouping) isnt needed as long as you put Time Range column as a filter on your matrix, and then you can just use:

SUM ( TABLE1 [Amount] )

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZM7bgMxDESvYmztgt+VxC4HCJDe8P2vkVlruIABVQ/8DTV8vQ61EsF7/P0ezyOGHu/nh+oq0U09buqzxDa1lU1zlDjpsE1Lz5JgWRuEniXJqsaqlVFyMn3uSPT3kkF4JqFj2tnpizC1ZHX3HemlgteRQmir9NbkhDFLKcn1/EDMMzBAR05CO0tbkXdkZCkVhe5G0BiQ3yt1QsNUoxsZYUDp7Jo7EnvD9lanD0ITvIZC6KusFUmn5yzrT2I65AwMQCgNHWWjt5SEiVGzv2M3woou+d2ooWOlVBSsOSutjIqU33GtHctn+lyELnhMX0IY4K3IN8RfCppZd8qmBgX+LRQ0sJYWxanQXhLmIz1XUwvs4NsmoOHl49tm151c9n/8fISJNTWYem3aPgUNjCGMTbnvBPJ00+ibwvfDBCxgfTw+MMSGbrPv5KwIFh19EgoPJSN99p3Alycj474TeH0Q0n9wJGalKNNB6FgBNcWEq97/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}, {"Amount", Int64.Type}}),
    #"Inserted Minute" = Table.AddColumn(#"Changed Type", "Minute", each Time.Minute([Time]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Minute", "MinutesToUse", each if
    [Minute] >= 0 and [Minute]<= TimeFrameMinutes
then
    TimeFrameMinutes
else
if 
    [Minute]> TimeFrameMinutes and [Minute] <=TimeFrameMinutes*2
then
    TimeFrameMinutes*2
else
if
    [Minute]> TimeFrameMinutes*2 and [Minute] <= TimeFrameMinutes*3
then
TimeFrameMinutes*3
else
0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Time Bucket End", each #time( Time.Hour([Time]), [MinutesToUse],0), Time.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Minute", "MinutesToUse"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Time Bucket Start", each [Time Bucket End] - #duration( 0,0, TimeFrameMinutes, 0), Time.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom2", {{"Time Bucket Start", type text}, {"Time Bucket End", type text}}, "en-US"),{"Time Bucket Start", "Time Bucket End"},Combiner.CombineTextByDelimiter(" to ", QuoteStyle.None),"Time Range"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Time Range"}, {{"Total Amount", each List.Sum([Amount]), type number}})
in
    #"Grouped Rows"

File:

https://1drv.ms/u/s!Amqd8ArUSwDS0FbWL-psD_C521Yq

[Anonymous]

Hi, Thank you for your replay i will take a look, but due to our high secrutiy levels at work i am unable to download the file. But i will follow your intructions and let you know.

[Anonymous]

Ah, that's a bummer.  But you can see the code above and I did a quick outline of the steps. if you want to go down this road and come across any issues, just post'em here. 

[Anonymous]

Hi,

Looked into this, and this wouldn’t work as I don’t have totals actually in a column. I have to use this measure below to get my actual total shipments. I want to somehow create a new table or two custom column's that would display the time and totals like the matrix table above. Sorry if I am not explaining myself clearly…

CoyTotal = calculate (countrows(ItemTracking), search("R***C-Normal*", ItemTracking[Merged],, 0)>0) + calculate (countrows(ItemTracking), search("LD***-Normal", ItemTracking[Merged],, 0)>0) + calculate (countrows(ItemTracking), search("SPUD***-Normal*", ItemTracking[Merged],, 0)>0) + calculate (countrows(ItemTracking), search("SVC***-Normal", ItemTracking[Merged],, 0)>0) + calculate (countrows(ItemTracking), search("R*5*C-MAX_R*", ItemTracking[Merged],, 0)>0)

[Anonymous]

Without seeing some sort of other data tables, it's pretty hard to see what you need to do. That formula, while it may make sense to you, is pretty much incomprehensible. The values come from somewhere and I think doing this strictly in DAX may be a challenge, though that's just my opinion. If you need the complex of a measure to generate the values seems like more of a data model issue than anything. I know it's hard to post any sort of data, but anything you can add to try and mimic what you have going on would be a tremendous help. My thought here is that there has to be a better way to get those values, but just a thought at this point. 

[Anonymous]

I think the main issue that i get is  an error when trying to group my time Colum.. it doesn’t seem to like it, that’s why i thought i could create a count on my 15 minute against my total pieces measure, to recreate a table that would display the data

[Anonymous]

I think I follow, but it's hard without getting into the data... 

Is the time columns  you are trying to group the CTime colum in your screenshot?  If so, think can jsut use the same kind of logic I presented earlier about how to get a column with the grouping ( i.e 9:00am to 9:15am) or just 9:00 or just the 9:15.  If you want to put this time range on rows in a table ( or any sort of filter) it has to be be a column. 

[Anonymous · Accepted solution]

its ok , i solved the issue i had a bug in my date column. Which i had to separate and then merge back. This then solved the issue with grouping my time.

Thank you so much for trying to help!

[Anonymous]

Hi

Below is the table that i use, for it to be classed as a succesfull shipment it has to follow this measure thay i have created to get total shipments. Somehow i want to have this total breakdown into 15 min windows in its own table or colums.

[Anonymous]

Aviation very high security but thank you