Forum Discussion
Relating Table dataset to a Matrix to perform calculations
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 😀
This measure should work as well:
zFlour = SUMX('Project', RELATED('Product'[Flour]))
7 Replies
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.
grantsambornSolution Sage
This measure should work as well:
zFlour = SUMX('Project', RELATED('Product'[Flour]))
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.