cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.