Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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?
Solved! Go to 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!
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
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.
Please take a look at the attached pbix file below and fire up Power Query and step through the applied steps:
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:
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.
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.
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)
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.
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
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.
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!
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.
Aviation very high security but thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |