cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Group By time 15 minute colum and total Measure

Hi,

i have a table that displays this data,

 

14:30:00166
14:45:0093
15:00:00126
15:15:003299
15:30:007184
15:45:005957
16:00:005026
16:15:004793
16:30:004046
16:45:002418
17:00:002794
17:15:003072
17:30:004942
17:45:004660
18:00:003391
18:15:004557
18:30:003957
18:45:003398
19:00:004066
19:15:007676
19:30:008174
19:45:009586
20:00:009189
20:15:008089
20:30:007354
20:45:007174
21:00:004867
21:15:0011124
21:30:0012479
21:45:0012287
22:00:0012587
22:15:0013006
22:30:0012815
22:45:0011781
23:00:006028
23:15:009619
23:30:0011003
23:45:009945
00:00:0010136
00:15:0010141
00:30:007644
00:45:007262
01:00:006953
01:15:007880
01:30:008913
01:45:008236
02:00:008191
02:15:008395
02:30:007446
02:45:008073
03:00:008344
03:15:005173
03:30:003387
03:45:004297
04:00:005998
04:15:005988
04:30:005336
04:45:002710
05:00:00823
05:15:00507
05:30:00180

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions

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!

View solution in original post

12 REPLIES 12
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Capture.PNGCapture2.PNGCapture3.PNG

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
Not applicable

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 

Final Table.pngGroupBy.png

 

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

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
Not applicable

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. 

Aviation very high security but thank you

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
Not applicable

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. 

 

 

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.

Capture3.PNG

 

Capture.5PNG.PNG

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors