Forum Discussion
Helper ITime spent on Tasks by each application
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.
2 Replies
v-deddai1-msftCommunity Support
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_qbUlArIIZbtCNiyUB12388jPdu-0kqb4IEZ2EeQ?e=qabI0d
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
nadirSWhat 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.