Selecting value from 1 column on the basis of other column value

[Harshul_Dhall]

I need to calculate the avg time taken by the docs at each level i.e. how much time it takes for an avg doc to clear the draft stage or In-approval level 1 stage.
I have different docs which have unique levels for each status (according to the process) and timestamp of how much time it took on each status-level.

In text format:-

Doc No.	Status	Level	Timestmap
1	Draft	1	10/19/2022 , 02:00:43 PM
1	In-approval	1	10/20/2022, 04:00:43 PM
1	In-approval	2	10/21/2022, 08:00:43 PM
1	In-approval	3	10/23/2022, 12:10:43 PM
1	In-approval	4	10/23/2022, 12:55:43 PM
1	Approved	1	10/24/2022, 01:20:23 PM
1	Approved	2	10/25/2022, 02:35:13 PM
1	Approved	3	10/26/2022, 04:45:33 PM
1	Signature	1	10/27/2022, 08:53:41 AM
1	Signature	2	10/27/2022, 08:53:41 PM
1	Signature verification	1	10/29/2022, 12:55:43 PM
1	Authorized	1	10/30/2022, 12:55:43 PM
2	Draft	1	10/19/2022 , 02:22:13 PM
2	In-approval	1	10/20/2022, 04:22:23 PM
2	In-approval	2	10/21/2022, 12:00:33 PM
2	In-approval	3	10/23/2022, 12:42:21 PM
2	Approved	1	10/24/2022, 01:20:23 PM
2	Approved	2	10/24/2022, 03:20:23 PM
2	Approved	3	10/25/2022, 02:35:13 PM
2	Approved	4	10/26/2022, 04:45:33 PM
2	Signature	1	10/27/2022, 08:53:41 AM
2	Signature verification	1	10/29/2022, 12:55:43 PM
2	Signature verification	2	10/29/2022, 08:25:26 PM
2	Authorized	1	10/30/2022, 05:30:00 PM

[v-tangjie-msft · Accepted solution]

Hi @Harshul_Dhall ,

According to your description, here are my steps you can follow as a solution.

(1) We can create calculated columns.

Rank by No = 
RANKX(FILTER('Data table','Data table'[Doc No.]=EARLIER('Data table'[Doc No.])),'Data table'[Timestmap],,ASC,Dense)

Duration = 
var _lasttimestamp=MAXX(FILTER('Data table','Data table'[Doc No.]=EARLIER('Data table'[Doc No.])&&[Rank by No]=EARLIER('Data table'[Rank by No])+1),'Data table'[Timestmap])
return
DATEDIFF('Data table'[Timestmap],_lasttimestamp,MINUTE)

(2)Then we can create a table.

Expected output = 
SUMMARIZE('Data table','Data table'[Doc No.],'Data table'[Level],"Duration(Hours)",AVERAGE('Data table'[Duration])/60)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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