Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
daciga
Frequent Visitor

Peak Hour Traffic

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.

 

peak-hour-traffic.png

2 ACCEPTED SOLUTIONS

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.

 

Hours 8.PNG

 

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"

hours9.PNG

So, now we have 23 calls.

 

2 tables

hours 10.PNG

 

hours 11.PNG

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

 

hours 12.PNG

 

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

 





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

Proud to be a Super User!




View solution in original post

Hi @daciga ,

 

PBIX for Peak Hours

 

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





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

Proud to be a Super User!




View solution in original post

19 REPLIES 19
btfly_x23
New Member

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?

@daciga @Nathaniel_C could you clarify my question above, please?

Nathaniel_C
Super User
Super User

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





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

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,

Screenshot 2019-09-15 at 7.33.37 AM.png

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? 





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

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.

daciga
Frequent Visitor

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]))

Hours.PNG

 

Hours1.PNG

 

Count = SUM('Calls'[Hour])

 





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

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:

Hours 3.PNG





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

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,

Hours 2.PNG

and created a measure,

Peak Hour Calls = max(Summary[High hour])

which I could display in card.

 

Hours 4.PNG

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

 

 

 

 

 





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

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

 

 

 

Hours 5.PNG

 

Hours 6.PNG

 

Hours 7.PNG

 





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

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





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

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.

 

Hours 8.PNG

 

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"

hours9.PNG

So, now we have 23 calls.

 

2 tables

hours 10.PNG

 

hours 11.PNG

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

 

hours 12.PNG

 

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

 





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

Proud to be a Super User!




Anonymous
Not applicable

@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 ,

 

PBIX for Peak Hours

 

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





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

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,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors