Trouble with multiplying ingredients by dishes served (Pivot Table)

[Dearayne]

Hello!

I'm the receiving manager at a restaurant and am trying to create a tool to aid in the ordering process. Basically, I want to multiply recipes by weekly sales data (what kinds of dishes were served how many times), to find out how much of each ingredient was (ideally) used to serve that food.

My idea was to have two tables:
-Ingredients by dish
-Sales by dish

I'd then add these two tables to a data model, creatings a relationship between the dish columns in each table, and use the model to create a Pivot Table so that I could multiply the ingredient quantities by the weekly sales of each dish.

However, I'm having some trouble with this.

I've got a workbook I'm using as a tester, but no luck.

I'm guessing my error is in how I'm setting up the ingredients table.

Here is the ingredients table of the tester:

And the sales data:

The relationship between the two tables:

Now, when I try to link the two together in a Pivot Table, weird stuff happens:

Instead of showing me how many times each ingredient was used, it just sums it all up.

Ideas?

Any help would be greatly appreciated.

Thanks and cheers!

 

[Derquila]

Maybe this:
View attachment 75261

Then pivot table:

 

[Derquila]

Try again (noobie):
This vlookup first:

Then pivot table above

 

[Dearayne]

Try again (noobie):
This vlookup first:
View attachment 75266
Then pivot table above

Brilliant, thank you!! Worked like a charm.