Matrix different subtotal row values

[Anonymous]

I have two tables.

Table 1 contains a list of projects. For each project it contains amount spent, budget and percent of budget.

Table 2 contains a list of project tasks. For each project tasks it contains amount spent, task budget and percent of task budget.

I want to build a matrix that shows "project" and "task" as the Rows, then shows amount spent, budget and percent of budget in the Values

When I drop in the Row "Project" and the Values "amount spent", "budget" and "percent of budget" from Table 1, it displays fine.

When I drop in the Row "Task" from Table 2 and expand then all the values for that row are the same as the "Project" row.

When I drop in the Task values then those populate into new columns on the matrix, and  the Project row acts as a subtotal.

I don't want the Project Row to be a subtotal of the Task row. I want the Project Row to have its own unique values from Table 1.

How do I do that?

Here's where I'm at.

for Project LA-0-22-00210 I want to keep the numbers in the bold row in the first three columns.

And then in the last three columns I want those values to be slid to the left into the first three columns.

Does that make sense?

[Anonymous]

Hi @Anonymous ,

If you could, please mark your reply as a solution, more people will benefit from it.

Best Regards,

Stephen Tao

[VahidDM]

Hi @Anonymous 

I think these links will be helpful:

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/

https://www.vahiddm.com/post/why-my-measure-returns-the-wrong-total

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Thanks, that's interesting but doesn't really accomplish what I'm wanting to do.

I ended up just creating two tables one showing the project values and one showing the individual task values. Not really what I wanted but it filled the need.