Average number of tickets by day of week

[warwick]

This is the format of my data, in my Tickets table.

I'd like to graph the average number of tickets by weekday over the past three months, i.e. On Mondays we get an average of 5 tickets logged.

I've read a couple of other posts and attempted to apply the logic to my situation but I'm having trouble doing so.

Any help would be appreciated!

[MattAllington · Accepted solution]

sorry - my bad.  I overlooked the fact that you wanted to report by day.  

Try this

Average Tickets 13 weeks =
    CALCULATE (
        DIVIDE ( [Total Tickets], DISTINCTCOUNT ( Calendar[WeekID] ) ),
        FILTER (
            ALL ( calendar ),
            calendar[WeekID]
                >= MAX ( calendar[weekID] ) - 12
                && calendar[WeekID] <= MAX ( calendar[WeekID] )
        ),values(calendar[day])
    )

where Calendar[Day] is the field you have on your chart axis.

[v-shex-msft]

Hi @warwick,

You can create a summary table to calculate the result:

Table.

Calculate table.

Formula:

Summary Table = DISTINCT(SUMMARIZE(ticket,ticket[Date],"Count",COUNT(ticket[TicketID])))

Calculate columns.

Average of last three Month = if(
AND([Date]>=DATEADD(LASTDATE(ALL(ticket[Date])),-3,MONTH),[Date]<=TODAY())&&AND(WEEKDAY([Date],1)<>1,WEEKDAY([Date],1)<>7),
AVERAGEX(
FILTER('Summary Table',
AND([Date]>=DATEADD(LASTDATE(ALL(ticket[Date])),-3,MONTH),[Date]<=TODAY())&&
WEEKDAY([Date])=WEEKDAY(EARLIER([Date]))),
[Count]),BLANK())

DayofWeek = SWITCH( WEEKDAY([Date],1),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5,"Thursday",6,"Friday",7,"Saturday")

Result:

Regards,

Xiaoxin Sheng

[warwick]

I'm getting an error here related to having duplicate dates in the date column, due to multiple tickets being logged on the same date at different times, know any way around that?

[MattAllington]

Who's instructions did you follow?  This should not happen with my approach.

[warwick]

Sorry, thought it would give the context as to who I was replying to when I clicked reply on Xiaoxin_Sheng's post. I tried your method as well, Matt see below:

Date Table created:

Average Tickets Measurement created on the tickets table:

Total Tickets measurement created on the tickets table (srsearchscreen)

And finally the graph:

However it isn't matching what we should be seeing, specifically a massive dip on Weekends. Additionally when I filter what we're seeing by the board the ticket is logged to the graph is blank.

[MattAllington · Accepted solution]

sorry - my bad.  I overlooked the fact that you wanted to report by day.  

Try this

Average Tickets 13 weeks =
    CALCULATE (
        DIVIDE ( [Total Tickets], DISTINCTCOUNT ( Calendar[WeekID] ) ),
        FILTER (
            ALL ( calendar ),
            calendar[WeekID]
                >= MAX ( calendar[weekID] ) - 12
                && calendar[WeekID] <= MAX ( calendar[WeekID] )
        ),values(calendar[day])
    )

where Calendar[Day] is the field you have on your chart axis.

[warwick]

Doesn't look like that's having the intended effect...

Feel free to have a go with this excerpt of data, https://www.dropbox.com/s/jvjnkq97gpkne4h/tickets.csv?dl=1

Once again, appreciate the help!

[MattAllington]

I have already tested it with my own data and it works.  If you would like further help, please post a sample of the workbook that isn't working so I can investigate. 

[MattAllington]

Yes, I saw that - but this is just a CSV file.  I can't check what is going wrong unless you post a workbook that has the DAX formulas.  As I mentioned, I have already tested it with my own data and it works perfectly.

[warwick]

Here is my PB workbook: https://www.dropbox.com/s/eb2qmunkit5whq5/Average%20TIckets%20by%20Weekday.pbix?dl=1

Using the csv from before as the datasource.

Thanks

[MattAllington · Accepted solution]

You have a time stamp as part of your date entered column in the tickets table.  If you don't need this time, then simply remove it during load (format as date). If you do need it, split the column before loading so you have a pure date column and a time column.  

[warwick]

Hi Matt,

See the link in my post just above yours for the data in question.

Cheers,

Warwick

[warwick]

That's fixed it. Thanks again for your help, @MattAllington

[GilesWalker]

@warwick - I have looked at your PBIX and there was an initial issue with the entered date in that it included time, I created a seperate colum to remove this so that way the Calendar table could link properly to the tickets table.

Thanks Giles

[MattAllington]

you need a calendar table first.  Make sure the calendar table has the day of week column included, plus a WeekID column that is 1 for the first week, 2 for the second week....8 for the 8th week, 9 for the 9th week etc.

read about that here http://exceleratorbi.com.au/power-pivot-calendar-tables/

Join the calendar table to your date column.

put the day of week on your chart axis.

write the following measures

Total Tickets = countrows(TicketTable)

Average Tickets 13 weeks =
    CALCULATE (
        DIVIDE ( [Total Tickets], DISTINCTCOUNT ( Calendar[WeekID] ) ),
        FILTER (
            ALL ( calendar ),
            calendar[WeekID]
                >= MAX ( calendar[weekID] ) - 12
                && calendar[WeekID] <= MAX ( calendar[WeekID] )
        )
    )

[ChrisDelgado24]

Im sorry, why is that you need to substract -12 in the formula?