Forum Discussion
Sum help with Many to Many Relationship
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
