Calculated table from 2 sheets with working filters

[RaedHussein]

Hi guys,

I have an excel sheet that contains 2 tables, one for the Product Material Cost, and the other for Sold Product, like this

Product Name	Material Required	Cost
Product 1	Material 1 (Wood)	12
Product 1	Material 2 (Iron)	4
Product 3	Material 1 (Wood)	14
Product 3	Material 4 (Paint)	20

Product Name	Selling Price	Order Number	Date of Selling	Location
Product 1	13	1		Amman
Product 1	13	2		Irbid
Product 4	19	3		Amman

What I need is to make a Calculated Table that Count Number of a sold product, Sum of Revenue, Sum of Material Cost and Profit for each product.

Also, this table is applied from slicers like Location or Material Required, etc

I really appreciate any help in that

[Jihwan_Kim]

Hi, @RaedHussein 

I am not sure about how your whole data model looks like, but I think there is some missing information in the sample.

Sales for Product 1 -> what is the material? is it wood, or is it Iron?

Sales for Product 4 -> I cannot know the cost based on the sample.

Thank you.

[RaedHussein]

Hi @Jihwan_Kim ,

That was just an example, but we can say that Product one made from wood and iron and other kinds of materials, so the cost of Product one should be the Cost of wood + the Cost of iron