PowerPivot: Many-to-Many Relationship

• Question

• Hi there,

Please check out the following database design: https://i.stack.imgur.com/d3NV5.jpg

I wish to do a store wise Material Usage analysis showing how much material should have been used at each store corresponding to the sales at that particular store as per the standard Recipe table.

My aim is to create a table like this: https://i.stack.imgur.com/nvdGZ.jpg

This requires a M2M relationship between table Sales and Recipe. Kindly help me create them.

Regards.

Wednesday, July 26, 2017 12:45 PM

• You should create a table for products with ProductID as unique identifier, and also a table for materials with MaterialID as unique identifier. Doing so, you can create relationships between all corresponding columns allowing you to filter on product, material, and store. Now,

ProductsSold:= SUM(Sales[Product_QTY])

is the number of products sold, and

MaterialInRecipe:= SUM(Recipe[Material_QTY])

is the amount of material; which is not useful by itself but can be used in another calculation. By iterating over the products table and combining both measures, you can compute the amount of material needed for the sales of a product:

MaterialNeeded:= SUMX(Products, [ProductsSold] * [MaterialInRecipe])

Filtering a store will impact [ProductsSold] to only sales at that store, and filtering a material will reduce [MaterialInRecipe] to only that material. In your example data, when filtering on material 12 and taking all stores, the result of [MaterialNeeded] is (1 * 300) + (1 * 345) + (1 * BLANK), or 645.

Monday, July 31, 2017 9:42 AM

All replies

• Hi Asimsidd,

According to your description, the first thing you need to do is to analyze the requirement of your customer. Then you can built the dimension tables and fact tables based on the requirement of your customer.

The tables provided by you are all transactional tables. You may need to convert the transactional tables to dimension tables and fact tables through ETL , whcih we call it as Data warehouse. Based on my understanding, you should build DimStore, DimMaterial, DimDate,DimRecipe and FactDailySales to feed your data model.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Thursday, July 27, 2017 2:30 AM
• You should create a table for products with ProductID as unique identifier, and also a table for materials with MaterialID as unique identifier. Doing so, you can create relationships between all corresponding columns allowing you to filter on product, material, and store. Now,

ProductsSold:= SUM(Sales[Product_QTY])

is the number of products sold, and

MaterialInRecipe:= SUM(Recipe[Material_QTY])

is the amount of material; which is not useful by itself but can be used in another calculation. By iterating over the products table and combining both measures, you can compute the amount of material needed for the sales of a product:

MaterialNeeded:= SUMX(Products, [ProductsSold] * [MaterialInRecipe])

Filtering a store will impact [ProductsSold] to only sales at that store, and filtering a material will reduce [MaterialInRecipe] to only that material. In your example data, when filtering on material 12 and taking all stores, the result of [MaterialNeeded] is (1 * 300) + (1 * 345) + (1 * BLANK), or 645.

Monday, July 31, 2017 9:42 AM