Measure Help

[GorD]

I have two tables one lists a Bill of Material, which has parent parts and components nos(which repeat) and component qtys. There are also some quantity modifiers that will add an additional requirement for expected losses etc. I have a measure that calculates the total required for each component

I have a second table that lists the components and facts about them - the main one being price

I need a measure ( which i'm struggling with) to show the cost of the components for each Parent part

So my pivot table would have

Parent Part, component part, calculated qty of components required, calculated cost of those components

Can't quite get this too work, but feel it should be easy

Help appreciated

Thanks

 

[Matt Allington]

Is there a many to 1 relationship between your 2 tables? Is does each item in the bill of materials table have an ID that joins it to the second table, and the key is unique in the second table? Is the parent part that key?

it would be helpful if you post a sample workbook with what you have got so far

 

[GorD]

Hi Matt, thanks for replying,

Each table has a component field. It is many on the BOM table and unique on the components table - I have created the relationship on this field. I'll try and post a file when I get home tonight.

Thanks again

P.S I'm on to chapter 4 of your book - very good

 

[GorD]

A sample file is here

https://www.dropbox.com/s/32jyueo13k1810q/Trial Bid Data with PQmeasures.xlsx?dl=0

Looking to create a measure showing total cost so component cost x qty(existing measure)

I think i'm also going to lose the disconnected table feeding the bid qty to the slicer and just have 5 different measures for total component qty and 5 different fields in the component table for price

 

[Matt Allington]

Glad you are liking the book. You need to read up on filter propagation (chapter 5 - you are nearly there)

and also make sure you get good syntax habits
Note the note at the bottom of page 34
Note Always refer to the table name and the column name when writing DAX. Never refer to a
column without also specifying the table name first. In future versions of Power Pivot, the table
name will be added automatically, but for now you need to manually type both the table name
and the column name. It will become clear why this is the case shortly.

Try this formula

=sumx(tblPrices,tblPrices[price1]*[TotCompBidQty1])

 

[GorD]

Thanks Matt, I have already picked up from chapter 4 (nearly finished) the tips on adding table names and using the measures dialogue box, rather than tying directly in the measures area and can now see the value in both.

Thanks again for the help and no doubt I'll some more questions shortly - hope you don't mind

 

[Matt Allington]

You can ask here and I often answer. Or on my forum PowerPivot Forum • Index page.

 

[GorD]

Glad you are liking the book. You need to read up on filter propagation (chapter 5 - you are nearly there)

Try this formula

=sumx(tblPrices,tblPrices[price1]*[TotCompBidQty1])

Hi Matt, I used this formula last week and have been trying to do some other things with the model - when i go back to this formula it has lost the pivot context - same result in each cell in the column - How can that be .

Hopefully when i finish chapter 5 i'll understand a bit better

 

[Matt Allington]

Ay chance you have deleted the relationship between tables?

 

[Matt Allington]

I wrote a detailed blog on the topic today if you are interested.

Many to Many Relationships in DAX Explained - Excelerator BI