Use a Measure to Sum Columns in Related Tables

[Anonymous]

I'm trying to use a measure to calculate the sum of a column in a related table two tables away. Basically I have a Product Table that has the ID of the product I want to get the cost of. It has a 1-to-Many relationship to a facility table which has the list of production facilities. That table has a Many-to-1 relationship to the Cost Table which shows the cost of each facility. 

I previously used calculated columns but am trying to just use measures. The issue is that I cannot seem to get it right. Either it creates duplicates when I put it into a table or or just repeats the same number for every product (effectively summing the entire cost table). 

Any help woudl be welcome. 

Product Table

ID

100

200

300

Facility Table:

ID	Facility
100	Prod1
100	Finish
200	Prod1
200	Finish
300	Prod2
300	Prod3
300	Finish

Cost Table:

Facility	Cost
Prod1	1000
Prod2	1200
Prod3	1400
Finish	500

Output of Measure through Power BI Table:

ID	Cost
100	1700
200	1700
300	3100

[Anonymous · Accepted solution]

Assuming this is how your data model looks:

You can use:

Total Cost = CALCULATE( SUM ( Cost[Cost] ), Facility)

[Anonymous]

So, I think that my database is a bit more complicated because it has some blanks when it comes to costs and facilities. 

The data model matched perfectly. 

Output I'm getting:

ID	Production Cost
100	1800
200	1800
300	1800
400	1800
500	1800
600	1800
700	1800
800	1800

Tables:

ID

100
200
300
400
500
600
700
800

KeyIDFacility

1	100
2	100
3	100
4	200	Prod1
5	200	Prod2
6	200	Prod3
7	300	Prod1
8	300	Prod2
9	300	Prod3
10	400	Prod1
11	500	Prod3
12	600	Prod1

Facility

Prod1

Prod2

Prod3