Trouble with multiplying ingredients by dishes served (Pivot Table)

Dearayne

New Member
Joined
Oct 2, 2022
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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:

1.PNG


And the sales data:

2.PNG


The relationship between the two tables:

3.PNG


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

5.PNG

10.PNG


11.PNG




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!
 

Attachments

  • 6.PNG
    6.PNG
    10.1 KB · Views: 9
  • 7.PNG
    7.PNG
    3.9 KB · Views: 14
  • 8.PNG
    8.PNG
    13.5 KB · Views: 16
  • 9.PNG
    9.PNG
    9.8 KB · Views: 15

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Try again (noobie):
This vlookup first:
vlookup.png

Then pivot table above
 
Upvote 0
Solution

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top