Average Daily Tickets by Category

[Anonymous]

Need a little DAX help, can't seem to create a measure to get the daily average back on a list of submitted tickets.   Seems like whatever I do I just seem to get a count of the tickets opened on a given day not the average for the month.  

Ticket Number     Date           Category
12345                   3/1/2019    Active Directory

Example Data:

1001      3/1/2019      Active Directory

1002      3/1/2019      Active Directory
1003      3/2/2019      Active Directory
1004      3/2/2019      Active Directory

1008      3/2/2019      Active Directory

1011      3/2/2019      Active Directory

For 3/1 there are 2 tickets that were opened, for 3/2 there were 4 opened 

Average for the month would be 3  (2+4/2)

Ultimately I want to build a graph showing each Category on the X axis and then the Averages for the month on the Y.

[Anonymous · Accepted solution]

Ok, 2 things to take apart here.  The first is the measure.

It sounds like you want the the SUM total number of tickets opened DIVIDED by the distinct number of days in the current filter context.

# Tickets per Day = 
VAR Tickets = 
	COUNTROWS(TableName)
VAR DistinctDays = 
	DISTINCTCOUNT(TableName[Date])
RETURN
DIVIDE(
	Tickets
	,DistinctDays
)

This will count the number of tickets opened, and divide it by the number of unique dates in the filter context.

Now, point 2.

You're not including Month in your visual at all, so my assumptions are as follows:

1. You have a calendar table related to your fact table and you've put a month slicer somewhere on the page
2. You have only 1 month of data in your fact table.

Just be aware that this measure will not slice by month on it's own, you have to introduce that in via a slicer or putting Month on the x-axis.

Hope this helps!

[Anonymous]

Chris,

  Thanks for the help, that worked perfectly, I had a page filter just for the month of March and was able to get a nice bar chart with exactly what I was looking for.  I kept thinking I had to perform some sort of AVERAGE or AVERAGEX operation on a COUNT but never got the result I was after, this was a way simpler approach.

Thanks,
Matthew

[Anonymous]

glad it worked @Anonymous!

I'd recommend a horizontal bar chart for that visual, it will keep the category titles horizontal (you can set the maximum width of the text values in the y-axis settings).

People read text faster when it's not rotated.  I forget the exact percentage, but numerous studies have concluded this 🙂

I was thinking of doing an AVERAGEX().  It may be faster, you'd have to check in DAX Studio:

Average # Of Tickets per Day = 
AVERAGEX(
	VALUES(TableName[Date]),
	CALCULATE( COUNTROWS(TableName) )
)

That should create a table of the unique dates in your filter context, count up all of the rows (ie, tickets), and then take the average of those counts.

I think this would be faster.  DIVIDE() forces a call to the formula engine, and I think you could keep everything in the storage engine with this AVERAGEX() method.