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

 

[GorD]

Thanks Matt, I'll take a look

 

[GorD]

EOQ and Pansize

Continuing with this task: I have this measure which calculates the tot number of each component that needs to be purchased for a bill of material (components can be repeated on different sub assemblies).

TotCompBidQty1:=roundup(sumx(tblBom,[Qty per Bom]*[Tot Yield factor])*[BidQty1(multiplier)],0)

I now need to extend this to include and MOQ or Pansize -

MOQ I guess would be a MAX or MAXX - how would this be added onto the existing measure

Pansize is a multiple ( could be 500 say), so everyting woulkd have to be in multiples of 500

Help appreciated

 

[Matt Allington]

Re: EOQ and Pansize

Can you explain what MOQ and Pansize are, and how they relate to your data model?

 

[GorD]

Re: EOQ and Pansize

Sorry Matt, an MOQ is a minimum order qty and a pansize would be when parts aren''t bought individually but in a multiple , maybe 500, this is likely to be because the components are bought on a reel and the smallest reel you can get is 500. Both MOQ and Pansize are fields with the Components table.

So I may have a component that is used on two sub assemblies - 5 per on one and 10 per on the other and i might be asked to build 100 units - we ( with your help previously calculated how many compoments we need ( without considering MOQ/Pansize) - (5+10)x100 = 1500. If the MOQ however is 2750 and the pansize is 500 - 1500 needed would be inflated to 2750 by the MoQ and then further inflated to 3000 by the pansize ( reels of 500)

I need to calculate the total components and then be able to report the BOM qty required ansds the qty caused by MOQ and pansize.

Hope that makes some sense - I'm not the best at explaining

p.s i could add a field to the component table combining the MOQ and pansize into a single multiplier if that is easier - is that bad practice?

 

[Matt Allington]

Re: EOQ and Pansize

O

p.s i could add a field to the component table combining the MOQ and pansize into a single multiplier if that is easier - is that bad practice?

Potentially yes. You would only do this if it makes life easier. If the 2 fields are in the same table and you can to do the calculation at run time, then I don't see a need. But the formula depends (always depends) on the data model. It is not possible to help without that information.

If you can take a screen shot of your tables laid out using the Collie Layout Methodology (read about that here) The Optimal Shape for Power Pivot Data - Excelerator BI
and post the image, also making it clear which tables each of the relevant columns are placed in, then I can give you some advice.

 

[GorD]

Re: EOQ and Pansize

Hi Matt and thanks, I have read the chapters on the Collie layout method, but I didn't quite know how to get it to fit my data, where i seem to have two main tables.

BOM table has the qty per assembly, components has the component prices and exchange rates as ex rate for the components.I also had a stand alone table that i was using as a lookup. A ink to my file is still available in post 4 if that helps.

Tables are:
https://www.dropbox.com/s/jkz5u0x5vc8jseh/Screenshot 2016-04-06 09.59.17.png?dl=0

Hope that helps you help me, many thanks

 

[Matt Allington]

Re: EOQ and Pansize

Does exchange rate vary over time? If not, why don't you just add the exchange rate to the prices table? If it does, the relationship is back to front.

Regarding your earlier formula, I would have thought you should replace the sumx table from tblBOM to TblPrices.


TotCompBidQty1:=roundup(sumx(TblPrices,[Qty per Bom]*[Tot Yield factor])*BidQty1(multiplier)],0)

If you do that, you can just multiple (or what ever) by the MOQ and Pansize columns

 

[GorD]

Re: EOQ and Pansize

The exchange rate is fixed for the bid - when you say add it to the prices table do you mean by vlookup or "related" function or typed?


In the Bom Table I have TotCompBidQty1:=roundup(sumx(tblBom,[Qty per Bom]*[Tot Yield factor])*[BidQty1(multiplier)],0) which calculates the total of each component required- before any MOQ or pansize modifications. Should i move this to the Prices table? Would that make it more straightforward?

In The tblPrices i have TotCompCostBidQty1:=sumx(tblPrices,tblPrices[price1]*[TotCompBidQty1]) which calculates the cost

So my measure TotCompBidQty1 (currently in the bom table) will say i need 2750 components and my MOQ in the components tbl may have a pansize of 3000 - How do i get the max of the two - guessing MAXX but can i use that with a measure as one of the arguments.

Help appreciated as always

 

[Matt Allington]

Re: EOQ and Pansize

The exchange rate is fixed for the bid - when you say add it to the prices table do you mean by vlookup or "related" function or typed?

Best to add it in the source table if you can. So depending on your source, yes vlookup or typed. It is better not to do a calc column (generally) but it would be fine in this case. It actually doesn't matter that much on a small model - it just simplifies things some.

In the Bom Table I have TotCompBidQty1:=roundup(sumx(tblBTotCompBidQty1:=roundup(sumx(tblBom,[Qty per Bom]*[Tot Yield factor])*[BidQty1(multiplier)],0) which calculates the total of each component required- before any MOQ or pansize modifications. Should i move this to the Prices table? Would that make it more straightforward?

so by "move to the prices table" you mean replace TblBOM with TblPrices, right? It is not that it is more straightforward, it is because it is more efficient (you should check that it works correctly first). It is complex to explain in a few words, but in short you can either work through the prices table one row at a time and then do a calculation on each row, or you can work through the BOM table one row at a time and do the calcs. If you have 1,000 records in the products table and 1,000,000 in the BOM table, there will be 1000x more calcs. The prices table iteration will only work if applying a filter on the prices table correct filters the BOM table ready for e calculation. Sorry - that may be a bit hard to understand without more detail.

If if the results are fast, you can just ignore this and leave it as is.

So my measure TotCompBidQty1 (currently in the bom table) will say i need 2750 components and my MOQ in the components tbl may have a pansize of 3000 - How do i get the max of the two - guessing MAXX but can i use that with a measure as one of the arguments.

the max functions you refer to works on columns, not between columns or on individual values. There are a few ways. You could create a calculated column that stores the largest of the two. =if([column1]>=[column2],[column1],[column2]). Generally this is not best practice but on small data models it is fine.

i think this will work too

TotCompBidQty rounded up:=roundup(sumx(TblPrices[Qty per Bom]*[Tot Yield factor])*[BidQty1(multiplier)]*if([column1]>=[column2],[column1],[column2]),0)

just make sure you specify the correct table[column] for each of your MOQ and Pansize.

Just test eat it and see how it goes.

 

[GorD]

Re: EOQ and Pansize

the max functions you refer to works on columns, not between columns or on individual values. There are a few ways. You could create a calculated column that stores the largest of the two. =if([column1]>=[column2],[column1],[column2]). Generally this is not best practice but on small data models it is fine.

i think this will work too

TotCompBidQty rounded up:=roundup(sumx(TblPrices[Qty per Bom]*[Tot Yield factor])*[BidQty1(multiplier)]*if([column1]>=[column2],[column1],[column2]),0)

just make sure you specify the correct table[column] for each of your MOQ and Pansize.

Just test eat it and see how it goes.

I was of the (miss)understanding that any formula with a double X had row context - which is why I thought MAXX would possible work. Ah well.

i'll give the if formulas a go

Thanks again