Forum Discussion

theBlessedCobba's avatar
theBlessedCobba
Frequent Visitor
1 year ago
Solved

Ticket Data Average per hour by weekday

I am trying to create a table that shows me the average count of tickets per hour by week day and im struggling  I have a full years worth of data and the question i want to answer is, across the ye...
Need Help
  • MattiaFratello's avatar
    MattiaFratello
    1 year ago

    Hi theBlessedCobba,

     

    Extract the weekday and hour from your timestamp column.

    Group data by weekday and hour.

    Count the number of tickets in each group for each day.

    Calculate the average count per hour per weekday over the entire year (or 12 months).

     

    Hour = HOUR('Tickets'[ticket_time])

    Weekday = FORMAT('Tickets'[ticket_time], "dddd") -- Full weekday name

    TicketsPerHour = COUNT('Tickets'[ticket_id])

     

    DailyHourCounts =
    SUMMARIZE(
    'Tickets',
    'Tickets'[Date], -- assuming you have a Date column or create one with DATE('ticket_time')
    'Tickets'[Hour],
    'Tickets'[Weekday],
    "CountTickets", COUNTROWS('Tickets')
    )

     

    AverageTicketsPerHour =
    AVERAGEX(
    FILTER(
    DailyHourCounts,
    DailyHourCounts[Hour] = SELECTEDVALUE('Tickets'[Hour]) &&
    DailyHourCounts[Weekday] = SELECTEDVALUE('Tickets'[Weekday])
    ),
    DailyHourCounts[CountTickets]
    )


    Create a matrix visual in Power BI:

    Rows: Hour

    Columns: Weekday

    Values: AverageTicketsPerHour measure

theBlessedCobba's avatar
theBlessedCobba
Frequent Visitor
1 year ago

Hi MattiaFratello or Shahid12523 

One thing i have noticed is if there is hour blocks where on mulitple weeks there is 0 tickets (eg 1am) then say once per month there will be 1 ticket, because the data set doesnt have the hour groups with the ticketcount of 0 in it, the avg shows >1 when really it should be 0.2 

Is there any way to cater for this? 

  • MattiaFratello's avatar
    MattiaFratello
    Icon for Super User rankSuper User
    1 year ago

    Hi theBlessedCobba, basically you want to have rows also for when ticket is = 0, correct?

    • theBlessedCobba's avatar
      theBlessedCobba
      Frequent Visitor
      1 year ago

      I am finding without those, i dont get a true average of that time window. 

      • MattiaFratello's avatar
        MattiaFratello
        Icon for Super User rankSuper User
        1 year ago

        Then basically you just need create the groups also for the zeros as well

         

        I think it's better to create a calendar table with all possible combinations

        Calendar =
        GENERATE(
        CALENDAR(MIN('Tickets'[ticket_time]), MAX('Tickets'[ticket_time])),
        ADDCOLUMNS(
        SELECTCOLUMNS(GENERATESERIES(0, 23, 1), "Hour", [Value]),
        "Weekday", FORMAT([Date], "dddd")
        )
        )

         

        And then you can count using your ticket table

        TicketsPerHour = CALCULATE( COUNTROWS('Tickets'), FILTER( 'Tickets', HOUR('Tickets'[ticket_time]) = Calendar[Hour] && 'Tickets'[Date] = Calendar[Date] ) )

         

        AverageTicketsPerHour = AVERAGEX( FILTER( Calendar, Calendar[Hour] = SELECTEDVALUE(Calendar[Hour]) && Calendar[Weekday] = SELECTEDVALUE(Calendar[Weekday]) ), [TicketsPerHour] )

        I haven't tested it tho, I have no time rn, please give it a try and adjust it accordingly