Forum Discussion
Link between column and rows for sumproduct
Hi all,
I am looking for some help for some science project. I have got some ingredients as powders with different fractions per size (Table 1). I have some recipes with different percentage of each ingredients (Table 2). I need to get a new calculated column with the sizing calculated for a filtered recipe. How can I link Table1 a and table 2 easily to get to the calculated column with a sumx of ingredient% from the recipe and % per size from the ingredient.
Thanks in advance for your inputs!
Table 1 | |||||
Size | Ingredient 1 | Ingredient 2 | Ingredient 3 | … | Ingredient 1000 |
1 | % | % | % | % | % |
2 | % | % | % | % | % |
3 | % | % | % | % | % |
4 | % | % | % | % | % |
5 | % | % | % | % | % |
6 | % | % | % | % | % |
… | % | % | % | % | % |
1000 | % | % | % | % | % |
Table 2 | ||||
Ingredient | Recipe 1 | Recipe 2 | … | Recipe 1000 |
Ingredient 1 | % | % | % | % |
Ingredient 2 | % | % | % | % |
Ingredient 3 | % | % | % | % |
… | % | % | % | % |
Ingredient 1000 | % | % | % | % |
New calculated column result needed | |
Size | Recipe X |
1 | % |
2 | % |
3 | % |
4 | % |
5 | % |
6 | % |
… | % |
1000 | % |
7 Replies
- amitchandak
Super User
Herbeaminou , I calculated the column you can get data from another table like
New column in Table 1
Sumx(Table2, Table2[Id] = Table1[ID]) , Table2[Value])
You can use a more complex formula or join. You can also use countx, minx or Maxx 
v-dineshyaCommunity Support
Hi Herbeaminou ,
Thank you for reaching out to the Microsoft Community Forum.
You want to link Table1 and table 2 to get the calculated column with a sumx of ingredient% from the recipe and % per size from the ingredient. Please refer below DAX code.
CalculatedSize =
SUMX(
Ingredients,
Table1[Size %] * RELATED(Table2[Recipe %])
)I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Thanks for the support.
That is actually what I was going for originally but could not get through as I cannot define the relation between the two tables. So the RELATED function cannot work. I found some other post with some suggestions of using a unpivot or so but I am not quite sure how to use it in my case. In excel I would be able to use the LOOKUP functions to solve this matter.
- v-dineshya
Hi Herbeaminou ,
Thank you for the update. As you mentioned in your previous response, you can able to solve the issue in excel, if still you want to resolve the issue in Power bi, Please share the sample data and share the sample output. It will help us to replicate the scenario.
Regards,
Dinesh
