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

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

    • theBlessedCobba's avatar
      theBlessedCobba
      Frequent Visitor
      1 year ago

      The ticketsPerhour measure doesnt allow for me to call the calender table in the filter for some reason

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

        Do you have a relationship btw the two tables?