cancel
Showing results for
Did you mean:
Frequent Visitor

How to calculate amount on each item based on its share

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

1 ACCEPTED SOLUTION
Super User IV

@emilmaican, try a new measure as

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

Proud to be a Super User!

3 REPLIES 3
Super User IV

@emilmaican, try a new measure as

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

Proud to be a Super User!

Frequent Visitor

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.

Frequent Visitor

I found the solution.  Thank you:

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

Announcements