Forum Discussion
Use a Measure to Sum Columns in Related Tables
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 |
2 Replies
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