Forum Discussion
Calculating Average Resolution Time for Tickets in Days
Hi
Im trying to calculate Average Resolution Time of Tickets in Days, Mins, Hours. How do I achieve this in PBI with DAX
Columns I have is Created Date, Resolved Date, Created Date (Worked), Resolved Date (Worked)
How do I calculate Average Resolution Time in Days. The goal is to show on an average how much time did a ticket take to get it resolved in days. Trying create a dashboard like as shhown below for my tickets
6 Replies
Anonymous
So if I understand correctly you'd want to have the same number in each row of the table? Ok, that can be done but it's not very efficient. Can you share the pbix and I'll build it there? Or if the data is confidential just share a pbix with mock data, with a few rows with the same format as your original table
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Hi Anonymous
Is each row in your fact table one different/unique ticket?
If so:
Measure = FORMAT ( AVERAGEX ( Table1, Table1[Resolved date] - Table1[Created date] ), "HH:MM:SS" )Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Thanks, but does this Dax return you the average number of DAYS it took to resolve?
Is each row in your fact table one different/unique ticket? Yes, each row is a different Incident that the person works on INC1, INC2.
When I used the above formula using Resolved Date and Created Date it gave me result as
Result:
Anonymous
The measure above is supposed to be used on it own, on a card visual for instance. Not in a calculated column. For that the code would need to be updated as it doesn't take context transition into account. What are you looking for, the same number (the overall average) in all rows?
You can include the days with a minor change (take the seconds out if you don't need them)
Measure = FORMAT ( AVERAGEX ( Table1, Table1[Resolved date] - Table1[Created date] ), "DD:HH:MM:SS" )Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Hi Sorry but im not so clear.
"For that the code would need to be updated" Im trying to create a calculated column which would give me average resolution in days for all tickets.
Can you help me with how can I create a calculated column for this and then I would want to show that in graph by pulling the fields into the pivots in PBI. Just like its shown the Graph image :- Average Time to Resolve overtime and other averages in hours.
