Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
Need help with my dax calculation. How do I get the Peak Hour Traffic calculation in power bi. Below is the sample metrics.
Thanks a lot.
Solved! Go to Solution.
Hi @daciga ,
Ok it looks like I used SUM at one point, when I should have used COUNT. So going back to the beginning I added more calls spread into the PM. Midnight is 12 AM by convention.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc+7DcQgDIDhVU7UKfALQrob4KTrUfZfI44dHET5f4Blek+QtoQH5s/3l86tJ7TmNpqsIc7Zu44W77hfrCXuV22a3u/eZXTT5mkeZAWZFoJ7Q8Ajv4I2Q+H/ANmbCdgAMEAcKKA4cEB1kIB9hbYA+qr6lwH3qnXaA3EFej7jQ84L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, Time = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Time", type time}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Time.Hour([Time])), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Hour"}}), #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Time] < #time(12, 0, 0) then 1 else 2), #"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "AM/PM"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"AM/PM", Int64.Type}}) in #"Changed Type1"
So, now we have 23 calls.
2 tables
This table created by:
Summary = SUMMARIZE(Calls,Calls[Hour],"HoursSummary",COUNT(Calls[Hour]))
Calls per hour = max(Summary[HoursSummary])
Lookup Hour = LOOKUPVALUE(Summary[Hour],Summary[HoursSummary],Summary[Calls per hour])
Lookup AM/PM = LOOKUPVALUE(Calls[AM/PM],Calls[Hour],MAX(Summary[Lookup Hour]))
Then measures:
Highest # of Calls per Hour = Max(Summary[Calls per hour])
Peak Hour for Calls = IF(Value(MAX(Summary[Lookup Hour]))>12,CONCATENATE(Value(Max(Summary[Lookup Hour]))-12," PM"), CONCATENATE(Value(max(Summary[Lookup Hour])), " AM"))
Total Calls = Count(Calls[Hour])
and the visuals
You may have to do a little work as the Time() function calls the hour from midnight to 1 AM the 0 hour as you can see by the top value in this picture.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi @daciga ,
Here is the file for what I have so far. What is the error that you are getting?
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
How did you calculate the HoursSummary - where does it refer to?
I have the same task to determine the Peak Hours Traffic, but, in my case, I do not have HoursSummary given. Therefore, I would be interested to know how can I display the Peak Hours in the visual "Card" without having this info "HoursSummary"? Or is it better to use charts for visualisation of peak hours?
Hi @daciga ,
We need more info, what does your data look like?
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hello Nathaniel,
Kilndly see attached CSV file. In the data its obvious that the Max Call Time is 2PM. What I need to know is the DAX formulation to find the desired computation. I have looked over the same topic here in PBI community but none of them captured my DAX problem.
Many thanks,
Hi @daciga ,
Thank you for the file, but I see no 2 PM calls. Also What is Group_time? So are you aggregating these by the hour?
Proud to be a Super User!
Hello Nathaniel,
Yes exactly. you may remove the group time since it should not be included in the calculation. What is needed is the number of calls per hour and which ever is the time with most calls (i.e. 2PM in my example) should be the "Max Call Time".
Other calculation here in PBI community only solved the average calls per time as shown in their computation. Whenever I tried the MAXIMUM formulation it only produces the maximum time (i.e. 24 Hours or simply 24) as the output.
Thanks again.
Its 2AM and not 2PM. My mistake.
Hi @daciga
I have a visual for you. First I added a new column and ended with table. The new column just peeled off the hour. I changed it to a whole number type. Then created a matrix by dropping Calls[Hours] which groups the different hours. Then created a measure that sums up the number of calls per hour. So as you see, 2 has the most calls 12.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
= Table.AddColumn(#"Changed Type", "Custom", each Time.Hour([Time]))
Count = SUM('Calls'[Hour])
Proud to be a Super User!
Hi @daciga ,
For the max count as a card.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Count max per Hour = MAXX(KEEPFILTERS(VALUES('Calls'[Hour])), CALCULATE([Count]))
Gives you:
Proud to be a Super User!
@daciga ,
Ok, try this:
New table which I called Summary, which I did in Power BI using SUMMARIZE()
Summary = SUMMARIZE(Calls,Calls[Hour],"HoursSummary",Sum(Calls[Hour]))
Then I added a column, using LOOKUPVALUE()
High hour = LOOKUPVALUE(Summary[Hour],Summary[HoursSummary],[Count max per Hour])
which gave me this table,
and created a measure,
Peak Hour Calls = max(Summary[High hour])
which I could display in card.
You could change it to Peak Hour or Peak Hour for Calls to be more descriptive.
Thanks for posting this interesting problem!
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi @daciga ,
Went back to Power Query, changed the type of Calls[Time] to text and added a new column with if Calls [Time] contains AM then 1 or else if Calls [Time] contains PM then 2 or else null. This way we can add AM/PM back in to the result.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTKyMjJQcPRVitWJVjIC800sYXxjMN8QLm8C4ZvD+KYQPly9GZhvCldvDuQbI+m3gPDNYHxLIN8EyTxDA6CAKcxBsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, Time = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Time", type time}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Time.Hour([Time])), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Hour"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Hour", Int64.Type}, {"Time", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "AM/PM", each if Text.Contains([Time], "AM") then 1 else if Text.Contains([Time], "PM") then 2 else null), #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"AM/PM", Int64.Type}}) in #"Changed Type2"
Then added a new column in Power BISummary[AM/PM2] to use LOOKUPVALUE () to search the CALLS table.
AM/PM2 = LOOKUPVALUE(Calls[AM/PM],Calls[Hour],Summary[High hour])
Then rewrote Peak Hour Calls to surround what we had with a CONCATENATE() and included an IF to pick AM or PM.
Peak Hour Calls = CONCATENATE(FORMAT(max(Summary[High hour]),"General Number"), If(MAX(Summary[AM/PM2]) = 1," AM"," PM"))
You can expand the if to include the null possibility.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hello again,
The computation worked!
However, when I add more calls or 5,000 calls for 7 days and use a slicer to group the calls per day, the peak hour card you made didnt work or remain static at 2AM. What could possibly went wrong?
Many thanks.
Hi @daciga ,
When you say you add more calls...do you mean calls on the same day? Or is it a second day that is the issue?
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi @daciga ,
Ok it looks like I used SUM at one point, when I should have used COUNT. So going back to the beginning I added more calls spread into the PM. Midnight is 12 AM by convention.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc+7DcQgDIDhVU7UKfALQrob4KTrUfZfI44dHET5f4Blek+QtoQH5s/3l86tJ7TmNpqsIc7Zu44W77hfrCXuV22a3u/eZXTT5mkeZAWZFoJ7Q8Ajv4I2Q+H/ANmbCdgAMEAcKKA4cEB1kIB9hbYA+qr6lwH3qnXaA3EFej7jQ84L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, Time = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Time", type time}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Time.Hour([Time])), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Hour"}}), #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Time] < #time(12, 0, 0) then 1 else 2), #"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "AM/PM"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"AM/PM", Int64.Type}}) in #"Changed Type1"
So, now we have 23 calls.
2 tables
This table created by:
Summary = SUMMARIZE(Calls,Calls[Hour],"HoursSummary",COUNT(Calls[Hour]))
Calls per hour = max(Summary[HoursSummary])
Lookup Hour = LOOKUPVALUE(Summary[Hour],Summary[HoursSummary],Summary[Calls per hour])
Lookup AM/PM = LOOKUPVALUE(Calls[AM/PM],Calls[Hour],MAX(Summary[Lookup Hour]))
Then measures:
Highest # of Calls per Hour = Max(Summary[Calls per hour])
Peak Hour for Calls = IF(Value(MAX(Summary[Lookup Hour]))>12,CONCATENATE(Value(Max(Summary[Lookup Hour]))-12," PM"), CONCATENATE(Value(max(Summary[Lookup Hour])), " AM"))
Total Calls = Count(Calls[Hour])
and the visuals
You may have to do a little work as the Time() function calls the hour from midnight to 1 AM the 0 hour as you can see by the top value in this picture.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
@Nathaniel_C Your answer helped me a lot to get started with my problem in hand.
I have posted it here, https://community.powerbi.com/t5/Desktop/Find-peakhour-and-PeakCount-based-on-time-based-data-for/m-...
Could you please help me on that?
I need to do the same but data needs to be filtered by selected Date as well. Thanks
Hi @daciga ,
Here is the file for what I have so far. What is the error that you are getting?
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
First of all, thank you for your effort into doing the calculation!
Some slight problem. I cannot seemed to go pass through the lookup hour calculation for I keep on getting an error message on that part.
n
Hello Nat,
Thank you for this excellent calculation! The next challenge would be is how to show in card the most frequent time or most calls that entered into the system say, 2AM in our data as the peak time.
Thanks,
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |