I am running into an issue trying to get the average time tickets are open during a range of time. What I have the report looking at is all tickets that are closed or opened during a period, calculating the time open, and then creating an average amount of time based on the number of tickets. My problem comes from the sum(Days Between) formula. If I create a table and have the tickets and the days between that information is correct. If I use the sum function for the column it gives me the correct number. The issue comes from the time it calculates without the table. That information is getting a far larger number.
I will list the relevant formulas here for reference:
Calculate Days Open per Ticket:
Measure Name: Days Between
=DaysBetween([Open Date];[Close Date])
Calculate Total Days Open:
Measure Name: Total Days Between
Count Total Tickets:
Measure Name: Count Tickets
Calculate Average of Days Open
Measure Name: Average Bays Between
=sum([Total Days Between]/[Count Tickets])
Below is an example of the data shown. I have truncated the information on the Example of Ticket Counted Data for simplicity. The Total Days between should be the same as the Days Between Sum.
What I need to put out is the average number of days that a ticket is open for the range of records chosen. I think the issue may stem from the tickets that are no closed but I am not certain. If anyone has any suggestions I would be very appreciative for hearing them.