Resource management - DAX to display "Project Demand (Hours)" and "Assigned Tasks (Hours)" in Matrix

[Deevo_]

Hi all,

I am unable to provide a pbix file, so I have added everything I can in here.

I am working on a report which looks like below matrix table, which is wrong. I would like output that looks like one of the two Ideal examples below this table.

Current output (wrong):

Project ID	Project Demand (Hours)	Staff Assignments (Hours)
AAA	100	100
Alice	100	50
Adam	100	50
Bob	100 (Bob should not appear under this ProjectID as they have no Assignments)	0
Bill	100 (Bill should not appear under this ProjectID as they have no Assignments)	0
BBB	50	50
Alice	50 (Alice should not appear under this ProjectID as they have no Assignments)	0
Adam	50 (Adam should not appear under this ProjectID as they have no Assignments)	0
Bob	50	25
Bill	50	25
Grand Total	150 (This is the SUM of the Project Demand Hours for each "Distinct" ProjectID (AAA + BBB))	150 (This is the SUM of "ALL" the staff Assignments for each ProjectID)

Ideal output version 1:

Project ID	Project Demand (Hours)	Staff Assignments (Hours)
AAA	100	100
Alice		50
Adam		50
BBB	50	50
Bob		25
Bill		25
Grand Total	150	150

Ideal output version 2:

Project ID	Project Demand (Hours)	Staff Assignments (Hours)
AAA	100	100
Alice	100	50
Adam	100	50
BBB	50	50
Bob	50	25
Bill	50	25
Grand Total	150	150

2 Datasets:

1) Project Data

ProjectID	Project Demand (Hours)
AAA	100
BBB	50

2) Staff Assignments Data

ProjectID	Staff Name	Staff Assignments (Hours)
AAA	Alice	50
AAA	Adam	50
BBB	Bob	25
BBB	Bill	25

Data Limitations:

• Project Demand:
• When we are making a decision on how many hours a Project requires, we do not know at this point who will be working on the project, hence we cannot match Project Demand with Staff Names.

Problem:

• I have created two separate measures:
• Project Demand = SUM(Project Demand (Hours))
• Staff Assignments = SUM(Staff Assignments (Hours))
• When I drag the Project Demand and Staff Assignments measures into the values section of the matrix, the totals look fine at the highest level of the hierarchy. It's when you drill down one level to the Staff Name, is where my problem lies.

What I would like help with:

• A DAX measure that only displays the Staff Name and their Assignment hours against a ProjectID "if the Assignments > 0" AND ignores the Project Demand component.

Thanks in advance, I hope I am communicating my requirements clearly.

[johnt75 · Accepted solution]

With a one-to-many relationship from the projects table to the staff assignments table you can create a couple of measures like

Project Demand Total = IF( NOT ISINSCOPE( 'Staff Assignments'[Staff Name]),
	SUM( 'Project Demand'[Project Demand (Hours)])
)

Staff Assignnments Total = CALCULATE(
	SUM( 'Staff Assignments'[Staff Assignments ( Hours )]),
	'Staff Assignments'[Staff Assignments ( Hours )] > 0
)

[Deevo_]

Hi John, I have just been doing some testing. From my matrix, I have exported the report data to Excel,  and the numeric values do not export at all. Do you know why your measure would be preventing the values from being exported?

[johnt75]

I haven't come across that issue before, but I rarely export to Excel.

[Deevo_]

No worries, I will keep digging for an answer

[Deevo_]

Hi John, Thank you for taking the time to read my long post! Thank you so much. Your solution has worked perfectly. I have been trying to solve this for a very very long time. I appreciate your help!