Peak Hour Traffic

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

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