Different roles workload calculation

[Anonymous]

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?

[TomMartens · Accepted solution]

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