How do I calculate a conditional sum for bill of material parts?

[t_guet01]

Hey guys,

I would like to calculate the aggregated price considering the individual quantities of single parts which build a parent. For example, the parent product "ABC" consists of the individual parts ABC1, ABC2, ABC3. ABC1 is required 2 times, ABC2 1 time, and ABC3 5 times to complete the parent product ABC. I would like to calculate a measure which states an aggregated price in case a price for all individual parts is given. The price for individual parts either comes from the PriceTable or the ManualAdditionsTable (where ManualAdditionsTable overrides the PriceTable).

So, for parent product ABC, the following price is correct: 2 * 110 (override the 120 from PriceTable) + 1 * 10 + 5 * 50

For parent product DEF, the price for the individual part DEF3 is missing, so I want the target measure to indicate a "n/a".

Desired solution:

Parent	Measure
ABC	480
DEF	n/a

Here are input data:

BOM:
load * inline [
Parent, SinglePart, Quantity
ABC,ABC1,2
ABC,ABC2,1
ABC,ABC3,5
DEF,DEF1,3
DEF,DEF2,1
DEF,DEF3,1
];

PriceData:
load * inline [
SinglePart, Price
ABC1,120
ABC2,10
ABC3,50
DEF2,1,40
];

ManualAdditions:
load * inline [
SinglePart, Price
ABC1,110
DEF1, 100];

I would prefer to solve this in a measure rather than rewriting the script.

Do you have any ideas?

Thanks and best

T

[edwin]

first off, you need to be aware that your two pricing tables will concatenate automatically - not sure if that is intended.

try this:

if(sum({<SinglePart={"=sum(Quantity*Price)=0"}>}1)=1,'NA',sum(Quantity*Price))

[edwin]

having said that the better way of doing this is to modify the DM and add a flag to the parent if one of the components is missing a price, then you use that flag as an exclusion - this way your chart runs faster

[sidhiq91 · Accepted solution]

@t_guet01  Please see below some of the changes that you might need to do in your script to get the desired output. And then I used the @edwin's expression to get the ouput.

Backend Script work.

NoConcatenate
BOM:
load * inline [
Parent, SinglePart, Quantity
ABC,ABC1,2
ABC,ABC2,1
ABC,ABC3,5
DEF,DEF1,3
DEF,DEF2,1
DEF,DEF3,1
];

PriceData:
load * inline [
SinglePart, Price
ABC1,120
ABC2,10
ABC3,50
DEF2,40
];

NoConcatenate
ManualAdditions:
load * inline [
SinglePart, Price
ABC1,110
DEF1,100];

NoConcatenate
Price_Temp_Table:
Load SinglePart as SinglePart1,
Price
Resident ManualAdditions;
Concatenate
Load *
Resident PriceData
where not Exists(SinglePart1,SinglePart);

Left join (BOM)
Pricetable:
Load Coalesce(SinglePart1,SinglePart) as SinglePart,
Price
Resident Price_Temp_Table;

NoConcatenate
Main_table:
Load SinglePart,
Parent,
Quantity,
If(isnull(Price),'N/A',Price) as Price
Resident BOM;

Drop table PriceData, ManualAdditions,Price_Temp_Table,BOM;

Exit Script;

Front End Expression: if(sum({<SinglePart={"=sum(Quantity*Price)=0"}>}1)=1,'NA',sum(Quantity*Price))

Please see the screen shot attached.

If this resolves your issue please like and accept it as a solution.

[t_guet01]

Hi,

thanks for the extensive code. Say I would like to stop before you left join the table to BOM: I tried to make the "where not exists" work without renaming the column to "SinglePart1" as this creates two columns "SinglePart1" and "SinglePart".

I want  a table with "SinglePart" and the "Price" - if available from ManualAdditions, if not from PriceData. I tried:

NoConcatenate
BOM:
load * inline [
Parent, SinglePart, Quantity
ABC,ABC1,2
ABC,ABC2,1
ABC,ABC3,5
DEF,DEF1,3
DEF,DEF2,1
DEF,DEF3,1
];

PriceData:
load * inline [
SinglePart, Price
ABC1,120
ABC2,10
ABC3,50
DEF2,40
];

NoConcatenate
ManualAdditions:
load * inline [
SinglePart, Price
ABC1,110
DEF1,100];

NoConcatenate
Price_Temp_Table:
Load *
Resident ManualAdditions;
Concatenate
Load *
Resident PriceData
where not Exists(SinglePart);

But that only gives me the data from ManualAdditions. What do I need to change?

Best

T