Product from tables related 1-n

[Alberto95]

Hi, i have a problem with the relationship 1-n. i need to calculate the product from different tables where in the first table i have the bill of materials and in the second tables i have the sales order with the quantity of each product ID. the key is Product ID. the structure is easy.

in the bill of materials each row contain the qty of materiuals used to do a 1 qty of product iD. (could be the percentege of material) 

bill of materials (1)

product id	qty_material1	qty_material2	qty_material3	qty_material4	qty_total
1A	10	50	30	110	200

sales orders: (n)

product id	qty_sales	date
1A	700	10.01.22
1B	150	15.01.22
1A	300	02.10.22

what i expect is, in the sales orders table: 

product id	qty_sales	date	Qty_mat_1	Qty_mat_2	Qty_mat_3	Qty_mat_4
1A	700	10.01.22	35	175	105	385
1A	300	02.10.22	15	75	45	165

thanks

Alberto

[v-zhangti]

Hi, @Alberto95 

You can try the following methods.
Measure:

%qty_material1 = DIVIDE(SUM('bill of materials'[qty_material1]),SUM('bill of materials'[qty_total]))

%qty_material2 = DIVIDE(SUM('bill of materials'[qty_material2]),SUM('bill of materials'[qty_total]))

%qty_material3 = DIVIDE(SUM('bill of materials'[qty_material3]),SUM('bill of materials'[qty_total]))

%qty_material4 = DIVIDE(SUM('bill of materials'[qty_material4]),SUM('bill of materials'[qty_total]))

Qty_mat_1 = [%qty_material1]*SUM('sales orders'[qty_sales])

Qty_mat_2 = [%qty_material2]*SUM('sales orders'[qty_sales])

Qty_mat_3 = [%qty_material3]*SUM('sales orders'[qty_sales])

Qty_mat_4 = [%qty_material4]*SUM('sales orders'[qty_sales])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alberto95]

is not working, attached the pbix with an example. 

the problem is when in the product id there is a qty = 0 of one material. idk why the measure still calculate the quantity. 

thanks 

Alberto

[v-zhangti · Accepted solution]

Hi, @Alberto95 

Because you only provided an id data at the beginning, it can't calculate it by group. The formula needs to be adjusted.

%qty_material1 = 
Var _N1=CALCULATE(SUM('bill of materials'[qty_material1]),ALLEXCEPT('bill of materials','bill of materials'[product id]))
Var _N2=CALCULATE(SUM('bill of materials'[qty_total]),ALLEXCEPT('bill of materials','bill of materials'[product id]))
Return
DIVIDE(_N1,_N2)

Other formulas and so on.

Qty_mat_1 = 
CALCULATE ([%qty_material1],
    FILTER (ALL ( 'bill of materials' ),
        [product id] = SELECTEDVALUE ( 'sales orders'[product id] )))
    * SUM ( 'sales orders'[qty_sales] )

 Other formulas and so on.

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alberto95]

Perfect. it works with this formula.