cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 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 😀

2 ACCEPTED SOLUTIONS
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

Solution Sage

This measure should work as well:

``zFlour = SUMX('Project', RELATED('Product'[Flour]))``
7 REPLIES 7
Post Patron

Accept the solution if worked 🙂

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

Post Patron

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

Regular Visitor

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.

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 ?

Regular Visitor

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.

Solution Sage

This measure should work as well:

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors