cancel
Showing results for
Did you mean:
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 + Plant MRP Type + Lot Size Reorder point Qty Plant # MRPT + LS Safety stock Max. level 10000031021 NDEX 2 0 1021 4 0 4 10000041021 NDEX 2 0 1021 4 0 4 10000061021 NDEX 2 0 1021 6 0 6 1000006310 PDEX 0 4 310 0 0 0 10000071021 NDEX 2 0 1021 6 0 6 10000081023 V1HB 1 0 1023 2 0 2 1000009820 PDEX 1 2 820 1 0 1

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)))

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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.

3 REPLIES 3
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.

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.