cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sharm
Frequent Visitor

Calculate average TAT for each category

Hi,

 

I have to calulate Average Turn Around time between DEACTIVATED & ACTIVATED status for each unique "Alert type".

 

Eg: Rows 1 to 26 is one Alert type, but i have many tickets Activated & deactivated for the same, All i need is to see how is the Average TAT time maintained in each alert type.

 

Also, If alert type doesnot have deactived status, the TAT should not be calculated...

 

sharm_0-1611597500905.png

 

I am not sure how to handle this case, Can anyone help with this

 

2 ACCEPTED SOLUTIONS
MFelix
Super User III
Super User III

Hi @sharm ,

 

What is the calculation you need to do exactly? do you need to calculate the time difference between each status and then the average?

 

Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Eyelyn9
Community Support
Community Support

Hi @sharm ,

 

According to my understanding, you want to calculate the average time calculated by deactived time - activated time of each AlertType ,right?

 

You could follow these steps:

1. Add a rank column to table:

Rank =
VAR a = [Alert Type]
VAR t1 =
    FILTER ( ALL ( 'Table' ), 'Table'[Alert Type] = a )
RETURN
    RANKX ( t1, RANKX ( t1, [Time],, ASC, SKIP ),, ASC, SKIP )

2. Use the following formula to create a calculated table:

New Table =
VAR _t1 =
    ADDCOLUMNS (
        SELECTCOLUMNS (
            CALCULATETABLE ( 'Table', 'Table'[Status] = "Activated" ),
            "AlertType", [Alert Type],
            "Ac", [Time],
            "Index", [Rank]
        ),
        "De",
            LOOKUPVALUE (
                'Table'[Time],
                'Table'[Alert Type], [AlertType],
                'Table'[Rank], [Index] + 1
            )
    )
RETURN
    ADDCOLUMNS ( _t1, "TimeDiff", DATEDIFF ( [Ac], [De], SECOND ) )

3. Calculate the average time

Average Measure =
CALCULATE (
    AVERAGE ( 'New Table'[TimeDiff] ),
    FILTER ( 'New Table', 'New Table'[AlertType] = MAX ( 'New Table'[AlertType] ) )
)

Or 

Average Column =
CALCULATE (
    AVERAGE ( 'New Table'[TimeDiff] ),
    ALLEXCEPT ( 'New Table', 'New Table'[AlertType] )
)

The final output is shown below:

1.28.3.1.PNG

 

Please take a look at the pbix file here.

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Eyelyn9
Community Support
Community Support

Hi @sharm ,

 

According to my understanding, you want to calculate the average time calculated by deactived time - activated time of each AlertType ,right?

 

You could follow these steps:

1. Add a rank column to table:

Rank =
VAR a = [Alert Type]
VAR t1 =
    FILTER ( ALL ( 'Table' ), 'Table'[Alert Type] = a )
RETURN
    RANKX ( t1, RANKX ( t1, [Time],, ASC, SKIP ),, ASC, SKIP )

2. Use the following formula to create a calculated table:

New Table =
VAR _t1 =
    ADDCOLUMNS (
        SELECTCOLUMNS (
            CALCULATETABLE ( 'Table', 'Table'[Status] = "Activated" ),
            "AlertType", [Alert Type],
            "Ac", [Time],
            "Index", [Rank]
        ),
        "De",
            LOOKUPVALUE (
                'Table'[Time],
                'Table'[Alert Type], [AlertType],
                'Table'[Rank], [Index] + 1
            )
    )
RETURN
    ADDCOLUMNS ( _t1, "TimeDiff", DATEDIFF ( [Ac], [De], SECOND ) )

3. Calculate the average time

Average Measure =
CALCULATE (
    AVERAGE ( 'New Table'[TimeDiff] ),
    FILTER ( 'New Table', 'New Table'[AlertType] = MAX ( 'New Table'[AlertType] ) )
)

Or 

Average Column =
CALCULATE (
    AVERAGE ( 'New Table'[TimeDiff] ),
    ALLEXCEPT ( 'New Table', 'New Table'[AlertType] )
)

The final output is shown below:

1.28.3.1.PNG

 

Please take a look at the pbix file here.

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

sharm
Frequent Visitor

Thanks @Eyelyn9 . It worked !

MFelix
Super User III
Super User III

Hi @sharm ,

 

What is the calculation you need to do exactly? do you need to calculate the time difference between each status and then the average?

 

Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

sharm
Frequent Visitor

Thanks @MFelix , It works

MFelix
Super User III
Super User III

Hi @sharm ,

 

Looking at the data you have provided you need to do the following steps on the query editor:

 

  • Group Row By category (don't forget to select All Rows in the operation)

 

  • Add an Index Column
  • Expand all column from the column that present the values Table
  • Sort Columns by (order is very important):
    • Index
    • Time Received

 

  • Add an Index column
  • Format the columns
  • Add the following custom column:
[Time Received] - (if [IndexAllRows] = 0 then null else if #"Changed Type" {[IndexAllRows] - 1} [Index] = #"Changed Type" {[IndexAllRows] } [Index] then #"Changed Type" {[IndexAllRows] - 1} [Time Received] else null )
  • Format the columns
  • If you want you can delete the index columns.

 

Now just load the values on the PBI and you can then create the following measure using dax:

Average time =
FORMAT (
    TIME ( TRUNC ( AVERAGE ( 'Table'[Duration previous stage] ); 0 ); (
        AVERAGE ( 'Table'[Duration previous stage] )
            - TRUNC ( AVERAGE ( 'Table'[Duration previous stage] ); 0 )
    ) * 60; 0 );
    "short time"
)

 

 

Check PBIX file attach.

 

If this works please tell me so I can post the answer and you can accept it so it can help others.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors