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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

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

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors