Time spent on Tasks by each application

[nadirS]

I am trying to calculate the time my team spends on different tasks - Tasks are recieved throughout the day (average about 500/day). There are two steps to completing each task, one person actions it and another one validates it - We support about a 100 different applications so each task can contain requests related to one application or multiple applications. Each task is created as a seperate item on a sharepoint list. There can be Four actions related to each task for each application: 

1) NoChange

2) Update

3) New Setup

4) Amend

Sharepoint setup: 

	Actioned By	Validated By	Application 1	Application 2	Application 3	Application 4	Application 5	Start Date	Complete Date
Task 1	John	Tom	NewSetup	NoChange	Update	NoChange	NoChange	10/5/2020	10/10/2020
Task 2	Tom	Stephanie	NoChange	NewSetup	NoChange	NewSetup	NoChange	10/9/2020	10/14/2020
Task 3	Stephanie	John	NoChange	NoChange	Amend	NoChange	Amend	10/7/2020	10/10/2020
Task 4	Jamie	Samantha	NoChange	Amend	NoChange	NewSetup	Update	10/8/2020	10/10/2020
Task 5	John	Melissa	NoChange	Amend	NoChange	NewSetup	NoChange	10/9/2020	10/10/2020
Task 6	Melissa	Ronald	Update	Amend	NewSetup	Update	Amend	10/5/2020	10/10/2020
Task 7	Ronald	Veronica	NoChange	NewSetup	NoChange	NoChange	NoChange	10/11/2020	10/19/2020
Task 8	John	Ahmed	NoChange	Amend	NoChange	NoChange	NoChange	10/5/2020	10/10/2020
Task 9	Ahmed	Tim	NewSetup	NewSetup	NoChange	NewSetup	NoChange	10/13/2020	10/18/2020
Task 10	Tim	John	Amend	NewSetup	Update	NoChange	NoChange	10/14/2020	10/19/2020

I have another Sharepoint list where we maintain an average of the time spent for each application, for example application1 new setup takes 120 minutes to action and 60 minutes to validate

Sharepoint Setup:

What i would ideally like to see is the following: 

Name	Action (minutes)	Validation (minutes)
John	540	155

The above calculation is as follows: 

Application1NewSetup	120		Application3Amend	75
Application1Update	90		Application5Amend	80
Application2Amend	120		Total Time Spent	155
Application4NewSetup	90
Application2Amend	120
Total Time Spent	540

Please let me know if this is doable in power bi or if you have any further question. 

[nadirS]

What you have done is correct for 1 application, i have more than 200 applications. How do i manage this with so many applications.  The logic is that each activity takes a different amount of time, Amend, Delete, Update, NewSetup.  

[v-deddai1-msft]

Hi @nadirS ，

What's your logic of calculation? Would you please explain more about it? Why there is Application1Update in your calculation for John?

For a preliminary understanding of your data, I transform your table 1:

And create a relationship between table1 and table2:

For more deatils, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQA8W3_qbUlArIIZbt...

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

Best Regards,

Dedmon Dai