Forum Discussion
Sum help with Many to Many Relationship
Hello,
I have two tables in a Many to Many Relationship. A BOM table that lists all the component parts needed to build a given finished part (including the quantity of each component part), and a table that contains all the purchase orders of the component parts by date and vendor. The tables are linked by the Part Id and the cross filter direction is Single (BOM Table filters PO table).
The reason this is Many To Many is that if I were to select multiple parent products to view at once, there may be multiple instances of the same part with different quantities and different parents, and on the PO side, there may be multiple vendors used to purchase the same part on a given day.
What I would like is to have a table where I can filter by finished part, and show the average prices by day for each part in the BOM. I have the average price measure worked out (by editing a quick measure), but I where I am getting stuck is displaying the individual BOM quantity for each part. When I display the BOM quantity it gives me the sum of all the part quantities, rather than the qty for each part as shown in the BOM.
Can anyone help me out with this? I am unsure if I should use a measure or calculated column, and none of my attempts at using Calculate/Filter, etc seem to be working.
Let me know if I need to give any more info, thanks!
My attempt to sketch it out:
BOM Table
| PartId | Qty | FinishedPartId |
| A | 1.25 | 1 |
| B | 5 | 1 |
| C | 0.5 | 1 |
| A | 2 | 2 |
| D | 10 | 2 |
PO Table
| PartId | Date | Qty | Price | Vendor |
| A | 1/1/2020 | 10 | 1.2 | 1 |
| A | 1/2/2020 | 5 | 6 | 1 |
| A | 1/2/2020 | 10 | 10 | 2 |
| B | 1/3/2020 | 5 | 20 | 3 |
Desired Final Table for Finished Part Id = 1
Currently the BOM Qty column is the sum of all three in every column.
| FinishedPartId | PartId | BOM Qty | Date | Weighted Unit Cost Avg |
| 1 | A | 1.25 | 1/1/2020 | 5 (not actual value) |
| 1 | B | 5 | 1/1/2020 | 5 |
| 1 | C | 0.5 | 1/1/2020 | 5 |
4 Replies
- Greg_Deckler
Community Champion
I will take a look if I have time, but many-to-many relationships are evil and you should avoid them unless you really know what you are doing.
Greg_Deckler Ok, yeah it may not be the Many to Many relationship that is causing my problem this time, but it does make me nervous. I will try and see if I can reduce some things.
Thanks.
v-alq-msftCommunity Support
Hi, larbpup
I wonder what the actual values of 'Weighted Unit Cost Avg' are. Based on your description, you may create two measures and a calculated column as below.
Calculated columns: TotalSales = PO[Qty]*PO[Price] Measures: DateMeasure = var _id = SELECTEDVALUE(BOM[PartId]) var _tab= CALCULATE( CONCATENATEX(PO,PO[Date],","), PO[PartId] = _id ) return _tab Avg = AVERAGE(PO[TotalSales])Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello,
Sorry, I should have clarified, I was using the values for weighted average in the initial post as a placeholder. Based on some testing, I think in my actual model that part is working correctly (although maybe it is still wrong since the BOMQty seems to be the total sum rather than the qty per part).Weighted Average:
PO_WeightedUnitCost = DIVIDE( SUMX( 'Purchase Orders', CALCULATE(SUM('Purchase Orders'[UnitCost]) * SUM('Purchase Orders'[Qty]) * sum(BOM[BOMQty])) ) , SUMX( 'Purchase Orders' , CALCULATE(SUM('Purchase Orders'[Qty])) ) )
I think I also should have been a little clearer on the column names. I have the PO Qty and I have the BOM Qty, both are used to caculate the average price per day. The PO Qty is just to help weight the price since there may be multiple prices for the same part on a given day. So my result table looks similar to yours, but I would like the BOM Qty to have the corresponding value per part, rather than the sum of the BOM Qty for all the parts.(52.46 is Total BOM Qty for all the purchase parts in Finished Part 1)
Finished P/N Purchase P/N Date BOM Qty Average Actual Unit Cost 1 A 4/1/2020 52.46 $9.20 1 B 4/1/2020 52.46 $99.67 1 C 4/1/2020 52.46 $13.48 1 D 4/1/2020 52.46 $14.82 1 E 4/1/2020 52.46 $1.30 1 F 4/1/2020 52.46 $14.85 1 G 4/1/2020 52.46 $1.20
