I'm servicing machines in multiple cities, and need to know how many parts to order for each city based on expected usage. The complication is that some models use parts more frequently than others. So I have two tables:
Population
| City | Model | Model Count |
| Miami | Model A | 1 |
| Miami | Model B | 2 |
| Jacksonville | Model A | 4 |
| Jacksonville | Model B | 3 |
Expected Usage
| Model | Part | Expected Annual Replacement |
| Model A | Part 1 | 2 |
| Model A | Part 2 | 1 |
| Model B | Part 1 | 7 |
| Model B | Part 2 | 2 |
What I need is a result that tells me what to stock:
| Miami | Part 1 | 16 |
| Miami | Part 2 | 5 |
| Jacksonville | Part 1 | 29 |
| Jacksonville | Part 2 | 10 |
(For example Miami has just one of Model A, which uses two of Part 1 each year, and two of Model B, which uses seven of Part 1. So it needs two of Part 1 to service the Model As, and fourteen of Part 2 to service the Model Bs. So Miami needs a total of sixteen of Part 1.)
I could join these tables on the non-unique Model field. That would create duplication, which is what I want in this situation. But Power BI doesn't allow joins on non-unique fields.
I could also create a relationship between each of these and a third table that's just "Model", but I can't seem to get that to work, either.
I'm open to doing this in different ways, so long as I end up with the result.
