Linking three tables in Power BI or Power Pivot

[Rafael135]

I have a restaurant and want to find out my theoretical ingredient use based on my recipes.

I have three tables,
1. MENU (pizza, lasagna)
2. RECIPES (8 ozs of ham per pizza)
3. CHECKS or INVOICES (for food served)
4. INGREDIENTS (ham, tomato, wine)

To find out how much ingredients I used I would link CHECKS or INVOICES ->MENU-> RECIPES->INGREDIENTS

Then I would multiply the number of items sold by the quantity per my recipe.

In regular Excel I would use sumifs() to find out how much ingredient I used. How do I do it in Power BI or Power Pivot ? Can I use DAX functions or should I use RELATE ?

RA

 

[Fazza]

hello, Rafael

One approach. This can be done in standard Excel and is an alternative to SUMIFS or other formulas.

Try working like a database.

[In fact, if you have MS Access: set up tables in Access and creates queries that join the tables as required. The same SQL that defines the relationships can be used in Excel, when you set up matching tables in Excel.]

So set up tables in Excel. Maybe one per worksheet, one each for MENU, RECIPES, CHECKS, INGREDIENTS.

Then you can join the tables using SQL, for either query tables, pivot tables, etc. There is a graphical interface through MS Query, so you don't need to know SQL.

HTH

 

[akice]

What you want can most certainly be done in Excel / Power Pivot - doesn't look too hard (knock on wood). If you can provide a workbook with sample data i should be able to return the pivot for you. Just need 4 tables with data.

As far as dabbling with MS Access that is a whole new ball game if you haven't worked with it (or with databases in general) before. Not sure you want to journey down that road...