Ticket Data Average per hour by weekday

[theBlessedCobba]

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! !

[theBlessedCobba]

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?

[theBlessedCobba]

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

[MattiaFratello]

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]

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

[MattiaFratello]

Do you have a relationship btw the two tables?

[theBlessedCobba]

There was a relationship but it didnt seem to work, 

i ended up using all the stuff that you gave me and ran it through an LLM a few times til i got the required result, really appreciate you help 

[Shahid12523]

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]

[MattiaFratello · Accepted solution]

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]

One 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 relationship

Use Day Name from your Sort Table in the final visualization

[theBlessedCobba]

Thank you!

[theBlessedCobba]

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 it