Link between column and rows for sumproduct

[Herbeaminou]

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	%

[v-dineshya]

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

[Herbeaminou]

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

[v-dineshya]

Hi @Herbeaminou ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[Herbeaminou]

No resolution yet. Trying some troubleshooting on my own. I will share more data.

[v-dineshya]

Hi @Herbeaminou ,

Thank you for the update. As you mentioned in your previous response, you have been  working on some troubleshooting steps to resolve the issue. Once you have done with your troubleshooting , please do let us know.

Regards,

Dinesh

.

[amitchandak]

@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