Trouble adding two sums

[SurfingData]

I have two tables: Product Cost and BtFormulaHdr.

I am working on product and labor cost for products.

I have successfully done the materials cost( which includes two steps: making a product that goes into another product) So there are raw materials and finished goods that then become inputs into a product. So there is a ratio to deal with.

Below is the dax and output for both. I have tried both sum and sumx and neither work correctly.

The Total Materials cost is accurate for both rows while the total labor cost is grabbing some of the information I want(202,702) but is not adding the other to it and the 0.00 is totally incorrect. Why is this occurring? Is it because the labor cost is working across two different tables?

[SurfingData · Accepted solution]

You need to check your relationships in the data model tab. The reason that the Total Materials Cost was working was that it was all within a table and so it had no reason to check relationship between the tables. However, the Total Labor used two separate tables and so the relationship mattered. There was no active direct relationship between the two tables so it was no able to grab the second table information.

So the answer to this problem  was that the relationships were not properly set  up! 

[aj1973]

Hi @SurfingData 

To my surprise is why are you trying to add Calculated Columns to your model if not going to use them as slicers/filters !!!?

Did you try using measures?

If you still want calculated coloumns, can you share a sample of your file?

[SurfingData]

I actually will end up using them as slicers and filters as I will build a report that will show the different inputs that go into each product and show a breakdown of the cost of each input at a fine grain level.

Everytime I try to make them as measures, they do not work. (I am  very new to Power BI and DAX).

I dont know how to make it filter on the formulaCode correctly because I can not sum or min or max it(It is a text field that looks like a number) .

I am open to using measures however if there is a way to do it that way that will still allow me to break down each product and all of its inputs.

[aj1973]

Fine, Can you share a sample of your PBIX? it would be faster and easier for me to help you.

[SurfingData]

I am unsure how to actually share a PBIX file with you. But more importantly, there is too much company data in this for me to feel comfortable sharing with you and I cant remove enough of what I would need to remove without it breaking many others things that are built upon it: the raw materials cost for example would stop working.

What I am trying to understand is: If you look at the pictures posted in the OP: If you take the top three rows of raw materials cost($0.00, $413.27, $1,224.33 ) and the materials cost finished goods( $287,323.14) it is the total Materials in the second row of that column ($288,960).  the $55 in the row above is also correctly aggregated from Raw Materials Column bottom six rows( $9.48, $1,087.97. $42,484.77, $3,939.02, $4,677.72, $3,082.01) . 

Why is the total Materials cost column working corrrectly while the Total Labor cost, which is essentially the same thing not?

THe Total Materials Cost column and the Total Labor cost column are both in the BtFormulaHdr table.