Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
06jakegillespie
Regular Visitor

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 IDProduct
1Chocolate Chip Cookie
2Cake
3Cake
4Brownie

 

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

 FlourEggChocolate ChipsVanilla
Chocolate Chip Cookie1211
Cake2101
Brownie1210

 

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 😀

2 ACCEPTED SOLUTIONS
srlabhe123
Post Patron
Post Patron

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

View solution in original post

This measure should work as well:

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

View solution in original post

7 REPLIES 7
srlabhe123
Post Patron
Post Patron

Accept the solution if worked 🙂

srlabhe123
Post Patron
Post Patron

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
Post Patron
Post Patron

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

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
Post Patron
Post Patron

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

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.

This measure should work as well:

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.