cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
alexmonegro Frequent Visitor
Frequent Visitor

Calculations depending on value in column in related table

I have table that looks like below (sample only, its a much larger dataset). The Mat + Plant column is the unique key; the Qty column is coming from another table joined on Mat+Plant that has mutliple records for each Mat+Plant, so every entry for Qty would be an index(match()) on the Mat+Plant key. I'll leave that out to minimize clutter. The rest of the columns are all in the same table.

 

Mat + PlantMRP Type + Lot SizeReorder pointQtyPlant #MRPT + LSSafety stockMax. level
10000031021NDEX201021404
10000041021NDEX201021404
10000061021NDEX201021606
1000006310PDEX04310000
10000071021NDEX201021606
10000081023V1HB101023202
1000009820PDEX12820101

 

I'd like to have a column either through a measure or otherwise that  achieved the following Excel calculation:

 

IF ([@[MRP Type + Lot Size]] = "PDEX", [@[Reorder Point]] - [@[Qty]],

   IF([@[MRP Type + Lot Size]] = "V1EX", [@[Safety Sotck]]-[@[Qty]],

     IF[@[Max. level]]>0, [@[Max. level]]-[@[Qty]],0)))

 

I have used the following DAX formula to get me mostly there, but can't seem to use the calcualted column I generated with it to substract the Qty value from it:

 

MRPT + LS = IF('MRP - Master'[MRP Type + Lot Size]="PDEX",VALUE('MRP - Master'[Reorder point]),IF('MRP - Master'[MRP Type + Lot Size]="V1EX",'MRP - Master'[Safety stock],IF('MRP - Master'[Max. level]>0,'MRP - Master'[Max. level],0)))

 

 

linkedin.com/in/alexmonegro
1 ACCEPTED SOLUTION

Accepted Solutions
alexmonegro Frequent Visitor
Frequent Visitor

Re: Calculations depending on value in column in related table

Seems like I'm still learning about the differences between measures and calculated columns. Was able to just create a new calculated column using this DAX formula: CALCULATE(SUM('Master Inventory by FT'[Qty])-SUM('MRP - Master'[MRPT + LS])).

 

This gave me the result I was looking at. However, I was not able to use a measure to create the same outcome. Not sure why.

linkedin.com/in/alexmonegro
3 REPLIES 3
Super User
Super User

Re: Calculations depending on value in column in related table

@alexmonegro I don't understand what exactly is the problem here. Testing it with your data and calculated column I can create calculated column and no issue wiht it.

alexmonegro Frequent Visitor
Frequent Visitor

Re: Calculations depending on value in column in related table

Should've clarified, what I need is to now create a measure or calculated column that takes the output of the calculated column and subtracts the Qty column. Something like this - new column = qty - existing calculated column.
linkedin.com/in/alexmonegro
alexmonegro Frequent Visitor
Frequent Visitor

Re: Calculations depending on value in column in related table

Seems like I'm still learning about the differences between measures and calculated columns. Was able to just create a new calculated column using this DAX formula: CALCULATE(SUM('Master Inventory by FT'[Qty])-SUM('MRP - Master'[MRPT + LS])).

 

This gave me the result I was looking at. However, I was not able to use a measure to create the same outcome. Not sure why.

linkedin.com/in/alexmonegro