How to calculate amount on each item based on its share

[emilmaican]

Hi,

I have a situation and I can't figure out how to solve it. I hope you can help me.

My model looks like this:

Table PRODUCTS sample (there are duplicated values on column ProductId):

- each product has a total production cost (on column Total cost)

Table RAWMATERIALSHARE sample (there are also duplicated values on column ProductId):

Each ProductId is made out of several RawMaterials. The split of cost for a certain ProductId by each RawMaterialId is shown on column "RawMaterialShare": 

I need to build a graph/chart showing the cost of each RawMaterialId by year

The result should be this:

Values	2018	2019	2020	Grand Total
Gold	2000	510	370	2880
Silver	2450	630	440	3520
Platinum	1250	380	230	1860
Copper	300	80	60	440
				8700

Thanks

[amitchandak · Accepted solution]

@emilmaican, try a new measure as

sumx(Summarize(RAWMATERIALSHARE, RAWMATERIALSHARE [productid], RAWMATERIALSHARE[rawmaterialid], "_1", sum(PRODUCTS[TotalCost]) * sum(RAWMATERIALSHARE[rawmaterialshare)]),[_1])

[emilmaican]

Thanks for the quick answer. I understood the principle. It seems there is something missing in your formula and I'm trying to solve it.

[emilmaican]

I found the solution.  Thank you:

CostByRawMaterial =
SUMX (
SUMMARIZE (
RAWMATERIALSHARE,
RAWMATERIALSHARE[productid],
RAWMATERIALSHARE[rawmaterialid],
"_1", SUM ( PRODUCTS[TotalCost] ) * SUM ( RawMaterialShare[RawMaterialShare] )
),
[_1]
)