Forum Discussion
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 year what is the average amount of tickets i get between 10-11am on a tuesday (as an example)
This is some of the dataset (there is a bunch more columns that can be used)
The result i am looking for is
| MON | TUES | WED | THU | FRI | |
| 1am | 2 | 2 | 2 | 1 | 1 |
| 2am | 2 | 2 | 2 | 1 | 1 |
| 3am | 3 | 3 | 3 | 2 | 2 |
| 4am | 4 | 4 | 4 | 6 | 6 |
| 5am | 5 | 5 | 5 | 3 | 3 |
| 6am | 6 | 6 | 6 | 4 | 4 |
| 7am | 20 | 20 | 20 | 10 | 10 |
| 8am | 50 | 50 | 50 | 30 | 30 |
with all values representing the average across the 12 months of that time on that day of the week
Thanks in advance! !
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
13 Replies
- MattiaFratello
Super User
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
This worked!!! thanks a lot
I guess i need to go manually validate its giving me the right answer but it looks correct
Ill mark it as accepted solution once i validate
Appreciate itOne last Question,
Is there a way for the data to be sorted Mon > Sun? i cant seem to get it to work
- MattiaFratello
Hey theBlessedCobba,
Create a Sort Table, for example
Day Name Day Order
Monday 1
Tuesday 2
Wed 3
....Click on your Sort Table column Day Name and sort it using Day Order
Connect this table to your other table with a relationshipUse Day Name from your Sort Table in the final visualization
- Shahid12523
Community Champion
Create Hour and Day columns from [opend].
Measure to count tickets:
Ticket Count = COUNTROWS('Tickets')
Measure for average across weeks/months:
Avg Tickets =
AVERAGEX(
SUMMARIZE(
'Tickets',
'Tickets'[Year],
'Tickets'[Week Number],
'Tickets'[Day],
'Tickets'[Hour],
"TicketsPerSlot", [Ticket Count]
),
[TicketsPerSlot]
)Use a Matrix:
Rows → Hour
Columns → Day
Values → [Avg Tickets] 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
Hi theBlessedCobba, basically you want to have rows also for when ticket is = 0, correct?
I am finding without those, i dont get a true average of that time window.
