cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SassySas
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:

AlertIDDeviceDevice Start TimeResult CodeClient Code
1SMS2019/08/04 11:06TNS 
1SMS2019/08/04 11:12TCS 
1  SM1 
1  SMDMessage successfully delivered.
1  SMDMessage successfully delivered.
2SMS2019/08/04 10:00TNS 
2SMS2019/08/04 10:15TNS 
2 2019/08/04 10:30TFS 
2  SMDMessage successfully delivered.
2  SMDMessage successfully delivered.
3SMS2019/08/04 12:30TNS 
3SMS2019/08/04 12:45TNS 
3 2019/08/04 13:00TCS 
3  SMDMessage successfully delivered.
3  SMDMessage successfully delivered.
4SMS2019/08/05 11:00TNS 
4SMS2019/08/04 11:15TFS 
4  SMDMessage successfully delivered.
4  SMDMessage 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.

 

Please, please. Your help is much appreciated 🙂 

Thank you in advance.

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: Calculate Average Response Time based on other columns

Hi @SassySas 

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

Measure =
VAR AuxTable_ =
    ADDCOLUMNS (
        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  Datanaut

Super User III
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] ) )
)

 

SassySas
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
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.

   

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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