Beginner question on Powerpivot relationships

[Castello]

Hi all,
I am new to powerpivot (and these forums), and would like to ask for little help regarding table relationships.


I have two tables, one that lists our projects. And another table that lists subprojects that are related to main projects.
Below you can see a (really simplified) example of the tables.


TableA: Projects


Project_id Subproject_id
1 A
2 B
3 C
4
5


TableB: Subprojects


Subproject_id Cost
A 5
B 3
C 3


I would like to create a measure that sums the costs of the subprojects on the project level.
Project cost:=Sum(Subprojects[Cost])


The problem is that some projects have no related subprojects, so when I try to create the relationship powerpivot automatically changes the direction of the relationship as it cannot create a 1-to-many relationship in the requested direction.

How should I set up the model in order to accomplish what I am trying to?

Thanks for your help!

 

[RoryA]

I think you really need the projectID to be in the subprojects table, rather than putting the subprojectid into the projects table.

 

[Castello]

I think you really need the projectID to be in the subprojects table, rather than putting the subprojectid into the projects table.

Thanks Rory, this solved my problem!