Forum Discussion

DiKi-I's avatar
DiKi-I
Icon for Post Partisan rankPost Partisan
2 years ago

Need help with the data and calculation

Hi, I have a ticket table with ticket creation date, resolve date and close date and its history table with history of tickets with different status in the lifecycle of ticket. Sample data attached...
Need Help
DiKi-I's avatar
DiKi-I
Icon for Post Partisan rankPost Partisan
2 years ago

This is not what my requirement is I have to show the average time when the ticket state was was open->active
similary average time when ticket was active->resolve.

Data-estDog's avatar
Data-estDog
Icon for Resolver II rankResolver II
2 years ago

You never mentioned average in your original post. You can go the direct dax route by using AverageX. 

'X' on a function calculates on a row by row basis before rolling it up into an aggregate. Precalculating the duration on a row level would still be more efficient. If you are dealing with a large number of rows on a highly used report you can do that later. Here is the DAX:

Avg Create to resolved = AVERAGEX(Sheet2, DATEDIFF(Sheet2[Create Date], Sheet2[resolved_date], MINUTE))

Please accept as solution to help others find quicker. Consider Kudos. 🙂



  • DiKi-I's avatar
    DiKi-I
    Icon for Post Partisan rankPost Partisan
    2 years ago

    I think I am not able to explain it properly.
    I have to calculate the average time of a ticket when status changes from new to active based on starttime and endtime in the history table. Simlarly I have to calculate the average time of ticket when the status of ticket changes from active to resolve based on starttime and endtime. How I can do this dynamically using measure? I have history of tickets with its life cycle.

    • Data-estDog's avatar
      Data-estDog
      Icon for Resolver II rankResolver II
      2 years ago

      So you are saying the Average Time of the day.... like 5:32 PM?

      So 10 tickets had a status change at 5:30 and 10 other tickets had a status change at 5:34, so the average is 5:32?

      If no, trying mocking up an example of the outcome you are looking for and posting it.

      • DiKi-I's avatar
        DiKi-I
        Icon for Post Partisan rankPost Partisan
        2 years ago

        Hi all,

        I have history data of tickets and I want to calculate two kpis one is how many tickets was fixed in first contact and another is the average time to acknowledge the ticket when it was created.
        Attached is the sample data. When a ticket arrives it gets assigned so the first record of the ticket will be used to find the average time to acknowledge the ticket and if a ticket has only one assignee/one record those will be first time fix.
        Can someone help me with the dax. This data will only have resolved tickets history.

        https://docs.google.com/spreadsheets/d/1BLONNk8-VkhHD5WGsFHchAKmfSdP7hNN/edit?usp=drive_link&ouid=108522559708375813161&rtpof=true&sd=true