cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emilmaican
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:

emilmaican_0-1609928760648.png

 

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

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

 

emilmaican_1-1609927550384.png

 

 

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": 

emilmaican_2-1609927579002.png

 

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

The result should be this:

Values201820192020Grand Total
Gold20005103702880
Silver24506304403520
Platinum12503802301860
Copper3008060440
    8700

 

Thanks

1 ACCEPTED SOLUTION
amitchandak
Super User IV
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!

View solution in original post

3 REPLIES 3
amitchandak
Super User IV
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!

View solution in original post

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.

I found the solution.  Thank you:

 

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors