Forum Discussion
Different roles workload calculation
Hi, I am trying to build a workload dashboard out of a project inventory list, this is how it looks like:
| PROJECT NAME | PROJECT STATUS | START | END | PROJECT DETAILS | OBJECTIVES & GOALS | PROJECT LEAD | Project Role | Value | Effort |
| A | Monitoring/Maintenance | 04/01/20 | 12/31/22 | John, Mary, Paul | |||||
| B | Execution | 03/01/19 | 11/01/23 | James | |||||
| C | Execution | 06/01/19 | 05/31/23 | Lily, Michael |
So far I am able to plot this Value vs Effort matrix below with ease, since I have both value and effort values for each project/row.
However, I would also like to proceed further by quantifying our workload of each project lead via "effort" points.
For every project/role, there will be several different people involved in it, each has a different role, e.g. for Project A, John will be the project manager (Effort point 5), Mary will be the Sponsor (Effort point 3), Paul will be the SME (Effort point 1).
| Project Lead | John | Mary | Paul | James | Lily |
| Effort Score |
Without changing the way we maintain the project inventory list, is there any DAX that can be utilized to visualize this clearly?
Hey Anonymous ,
my understanding is that the people involved with the project are stored as comma separated list, meaning Project A has 3 people, and Project B has only 1 (James). This is an assumption based on the sample data visualized in the screenshot from your initial post.
I do not fully understand how it is possible to determine the role the person is playing in the project assigning the effort points to the role the person.Depending on your ideas of the further development changing the structure of your project inventory list or at least deriving a proper data model. This is because multi-valued cells like Project Lead (assumption based on the data visualization in the screenshot) are only a simple "string" in the tabular world.
Consider creating a pbix file that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures), upload the pbix file to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data (instead of the manual input method) share the Excel file as well.
Next, elaborate on the ideas of how you want to proceed your existing solution.
Regards,
Tom
1 Reply
Hey Anonymous ,
my understanding is that the people involved with the project are stored as comma separated list, meaning Project A has 3 people, and Project B has only 1 (James). This is an assumption based on the sample data visualized in the screenshot from your initial post.
I do not fully understand how it is possible to determine the role the person is playing in the project assigning the effort points to the role the person.Depending on your ideas of the further development changing the structure of your project inventory list or at least deriving a proper data model. This is because multi-valued cells like Project Lead (assumption based on the data visualization in the screenshot) are only a simple "string" in the tabular world.
Consider creating a pbix file that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures), upload the pbix file to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data (instead of the manual input method) share the Excel file as well.
Next, elaborate on the ideas of how you want to proceed your existing solution.
Regards,
Tom