cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate Average Response Time based on other columns

I am trying to work out the average response time to automated SMS's. The problem is my records are a one to many relationship on the alertID, so one AlertID can have many records, as the records show the sequence of events for that alertID.

So the Sent time is the row where the FIRST/earliest result code = TNS, there can be multiple TNS result codes for the same alert, I need the first one. Then from that first time TNS record the device start time is the sent time.

Then the response time is the record where result code = TCS or TFS

So the average response time is response time – sent time

I am really struggling to find the correct power bi logic to do this.

Below is a sample of my data:

 AlertID Device Device Start Time Result Code Client Code 1 SMS 2019/08/04 11:06 TNS 1 SMS 2019/08/04 11:12 TCS 1 SM1 1 SMD Message successfully delivered. 1 SMD Message successfully delivered. 2 SMS 2019/08/04 10:00 TNS 2 SMS 2019/08/04 10:15 TNS 2 2019/08/04 10:30 TFS 2 SMD Message successfully delivered. 2 SMD Message successfully delivered. 3 SMS 2019/08/04 12:30 TNS 3 SMS 2019/08/04 12:45 TNS 3 2019/08/04 13:00 TCS 3 SMD Message successfully delivered. 3 SMD Message successfully delivered. 4 SMS 2019/08/05 11:00 TNS 4 SMS 2019/08/04 11:15 TFS 4 SMD Message successfully delivered. 4 SMD Message successfully delivered.

I am literally just trying to calculate on average how long does it take to get a response once the first SMS is sent, to when the response is received.

Thank you in advance.

4 REPLIES 4
Highlighted
Super User III

## Re: Calculate Average Response Time based on other columns

I assume it's average across the IDs that you are looking for:

```Measure =
VAR AuxTable_ =
DISTINCT ( Table1[AlertID] ),
"ResponseTime",
VAR Sent_ =
CALCULATE (
MIN ( Table1[Device Start Time] ),
Table1[Result Code] = "TNS",
ALLEXCEPT ( Table1, Table1[AlertID] )
)
VAR End_ =
CALCULATE (
MIN ( Table1[Device Start Time] ),
Table1[Result Code] IN { "TCS", "TFS" },
ALLEXCEPT ( Table1, Table1[AlertID] )
)
RETURN
End_ - Sent_
)
RETURN
AVERAGEX ( AuxTable_, [ResponseTime] )
```

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers

Super User III

## Re: Calculate Average Response Time based on other columns

or, equivalently, a more compact albeit probably less readable version:

```Measure =
AVERAGEX (
DISTINCT ( Table1[AlertID] ),
CALCULATE ( MIN ( Table1[Device Start Time] ), Table1[Result Code] IN { "TCS", "TFS" }, ALLEXCEPT ( Table1, Table1[AlertID] ))
- CALCULATE ( MIN ( Table1[Device Start Time] ), Table1[Result Code] = "TNS", ALLEXCEPT ( Table1, Table1[AlertID] ) )
)
```

Frequent Visitor

## Re: Calculate Average Response Time based on other columns

Hi !

Thank you for your help. What type does the calculation return?

I tried both methods and they give me a result of 26 864.18,

Which seems like a rather large average response time haha

Super User III

## Re: Calculate Average Response Time based on other columns

The result will be in days since that is the base for the Datetime type. You can then convert it to mins, secs,

With the data you provided I get an average of -0,24 days but that is because id 4 seems to be travelling back in time:

 4 SMS 2019/08/05 11:00 TNS 4 SMS 2019/08/04 11:15 TFS 4 SMD Message successfully delivered. 4 SMD Message successfully delivered.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors