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.

4 REPLIES 4
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_ =
"ResponseTime",
VAR Sent_ =
CALCULATE (
MIN ( Table1[Device Start Time] ),
Table1[Result Code] = "TNS",
)
VAR End_ =
CALCULATE (
MIN ( Table1[Device Start Time] ),
Table1[Result Code] IN { "TCS", "TFS" },
)
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

Highlighted
Super User III

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

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

```Measure =
AVERAGEX (
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

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors