Dax formula to calculate product blending

[Anonymous]

I have two tables, Sales and Recipe. The recipe table has percentages of each ingredient needed to make the products and it could take up to three different ingredients to make a certain product. The sales table has the pounds sold of each product. I need a DAX formula to look at the sales amount for each product and calculate the total pounds of each ingredient needed to make the products sold.

Recipe_Table
Prod Code	INGRED_1	INGRED 1%	INGRED_2	INGRED 2%	INGRED_3	INGRED 3%
A	1	34%	8	65%	9	1%
B	2	51%	3	39%	9	10%
C	3	80%	5	20%
D	4	65%	1	33%	9	2%
E	2	51%	4	49%
F	2	51%	5	49%
G	7	42%	8	42%	6	16%
Sales_Table			Dax formula to
Prod Code	Tot Lbs		calculate Ingred needed
A	100000		Ingred	Ingred Needed
B	121000		1	50500
C	98000		2	116280
D	50000		3	125590
E	65000		4	64350
F	42000		5	40180
G	200000		6	32000
Total	676000		7	84000
			8	149000
			9	14100
			Total	676000

[MFelix · Accepted solution]

HI @Anonymous ,

Your Recipe table should have the following format:

Prod CodeINGRED%

A	1	0,34
B	2	0,51
C	3	0,8
D	4	0,65
E	2	0,51
F	2	0,51
G	7	0,42
A	8	0,65
B	3	0,39
C	5	0,2
D	1	0,33
E	4	0,49
F	5	0,49
G	8	0,42
A	9	0,01
B	9	0,1
D	9	0,02
G	6	0,16

Then make a relationship between recipe and sales and add the following measure:

ingridient quantity = SUMX(Recipe;RELATED(Sales_Table[TOT LBS])*Recipe[%])

Check the PBIX with the transformation of the recipe table and the result of the measure I post above.

Regards,

MFelix

[d_gosbell · Accepted solution]

So if you pivot your recipe table out to look like the following you can do this with the measure below

Required Product = SUMX(Sales, 
var _TotLbs = Sales[Tot Lbs]
var _ProdCode = Sales[Prod Code]
return SUMX( CALCULATETABLE(Recipe, TREATAS({_ProdCode}, Recipe[Prod Code]))
       , Recipe[INGRED %] * _TotLbs)) 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[d_gosbell · Accepted solution]

So if you pivot your recipe table out to look like the following you can do this with the measure below

Required Product = SUMX(Sales, 
var _TotLbs = Sales[Tot Lbs]
var _ProdCode = Sales[Prod Code]
return SUMX( CALCULATETABLE(Recipe, TREATAS({_ProdCode}, Recipe[Prod Code]))
       , Recipe[INGRED %] * _TotLbs)) 

[Anonymous]

Thank you d_gosbell for your solution. All the replies would have worked but I like the flexibility yours provided.

Thanks to all that responded.

[d_gosbell]

Note that the solution by @MFelix is a simpler approach to mine, but it does assume that your Sales table only has unique product code, If your Sales table will have other fields (eg. if you record the amount sold by date) then you will not be able to use a relationship and the related() function and will probably need to look at something like my suggestion. 

[MFelix · Accepted solution]

HI @Anonymous ,

Your Recipe table should have the following format:

Prod CodeINGRED%

A	1	0,34
B	2	0,51
C	3	0,8
D	4	0,65
E	2	0,51
F	2	0,51
G	7	0,42
A	8	0,65
B	3	0,39
C	5	0,2
D	1	0,33
E	4	0,49
F	5	0,49
G	8	0,42
A	9	0,01
B	9	0,1
D	9	0,02
G	6	0,16

Then make a relationship between recipe and sales and add the following measure:

ingridient quantity = SUMX(Recipe;RELATED(Sales_Table[TOT LBS])*Recipe[%])

Check the PBIX with the transformation of the recipe table and the result of the measure I post above.

Regards,

MFelix