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

View solution in original post

3 REPLIES 3
ankitpatira Super Contributor
Super Contributor

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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 43 members 1,097 guests
Please welcome our newest community members: