Relating Table dataset to a Matrix to perform calculations

[06jakegillespie]

I need to create a relationship between two data sets that are different format to perform a calculation.

Unfortunately I cannot share specifics for data privacy reasons so I will attach a pseudo-set

My Table will look something like this 

Project ID	Product
1	Chocolate Chip Cookie
2	Cake
3	Cake
4	Brownie

My table will be related to a matrix to keep track of the "ingredients" required for each model

	Flour	Egg	Chocolate Chips	Vanilla
Chocolate Chip Cookie	1	2	1	1
Cake	2	1	0	1
Brownie	1	2	1	0

Where:

x1 - xn is a horizontal list of all our ingredients

y1 - yn are all the different products

We want a sum of all the products... Countrows(Table1[Products])

So from our table, if we wanted to see what we needed to order for all of our cake requests

The desired output would be

4 Flour

2 Egg

0 Chocolate Chips

2 Vanilla

It needs to respond to filtering, so we can filter by Quarter, and know how many ingredients to order for that Quarter. I imagine you do this through a measure.

I have a working model in excel that functions calculates as I want, but I am new to Power BI and would like to create a dashboard system once I can calculate the values I need. 

If any necessary information is needed, please let me know. Thank you 😀

[srlabhe123 · Accepted solution]

HI

 Create a measure say ProdCount=count(Product) with its name so for ex for Cake- 2 and so on.

Then just mulitiply it like ProdCount* No of Ingredients required say for Cake it has to be

2* 2 (Flour) and so on, looks like its simple maths.

Check with simple aggregate function Count

[srlabhe123]

Accept the solution if worked 🙂

[srlabhe123 · Accepted solution]

HI

 Create a measure say ProdCount=count(Product) with its name so for ex for Cake- 2 and so on.

Then just mulitiply it like ProdCount* No of Ingredients required say for Cake it has to be

2* 2 (Flour) and so on, looks like its simple maths.

Check with simple aggregate function Count

[srlabhe123]

I would suggest you to calculate count of Orders and then multiply it with Ingradients

[06jakegillespie]

Yes, exactly.  What is the syntax for a measure that will do that? 

I can use Countrows(Table[Product]) to get a count for each of the orders per product.

But I do not know how to multiply by the product name in the matrix

In excel I can xlookup by the product name then cross check and return the product of the two.  I dont know if DAX has a similar function. 

[srlabhe123]

Hi did not understand why woudl you need 4 Floor and 2 Eggs for Cackes, can you please elaborate what youyr excel contains ?

[06jakegillespie]

Our table is a list of all the "orders" we have to purchase ingredients for. So in the first table, we have 2 cake orders.  

Our matrix tells us: For each cake, we need 2 flour, 1 egg, and 1 vanilla.

My Excel data is a workbook with 3 sheets:

- The Table of our orders

- Our ingredient matrix

- Quantities of ingredients to order

So if we need to buy ingredients for our 2 cake orders, we would need 4 flour, 2 eggs, and 1 vanilla.

[grantsamborn · Accepted solution]

This measure should work as well:

zFlour = SUMX('Project', RELATED('Product'[Flour]))