How to Look at one row in a set and select other rows from which to extract fields for calculations

[Shelley]

Hi All,

I'm looking for some help in selecting data from one row, using it to select data in another row, and then performing a calculation. We have contract data. Each contract can have multiple Main lines and then each Main line has corresponding Sublines. I need to take data from a specific Main line and find the first corresponding subline, and use data from it to perform a calculation.

Contract Number	Line Item Number	Material ID	Material	Main/Sub Indicator	Line Item Target Quantity	Line Item Target Quantity UOM	Line Item Target Value
41596	100	PN-C01522	Maintenance Contract Service	M	1	EA	54815
41596	110	PN-C20155	Contract 1.0 Labor	S	330.11	STD	54815.12
41596	120	PN-C20155	Contract 1.0 Labor	S	1	STD	166.05
41596	130	PN-C20161	Contract 1.5A OT Labor	S	1	STD	249.08
41596	140	PN-C20161	Contract 1.5A OT Labor	S	1	STD	249.08
41596	150	PN-C20163	Contract 2.0A OT Labor	S	1	STD	332.1
41596	160	PN-C20163	Contract 2.0A OT Labor	S	1	STD	332.1

1. First I need to find Main/Sub Indicator = M that also has PN-C01522 and use the Line Item Target Value

2. Then, given the Main line that matches this criteria is line item number 100, find the next in sequence Sub-Line Item Number (in this case, 110), so Main/Sub Indicator = S and ensure the material ID is PN-C20155 and grab the Line Item Target Quantity of 330.11.

3. Then I want to divide the Line Item Target Value by the Line Item Target Quantity.

The results would look like this (1st row, based on the above):

Contract Number	Line Item Target Value	Entitlement Qty	Per Unit
41596	54815	330.11	166.05
99999	2500	25	100.00
88888	6000	58	103.45

Anyone know some easy way to do this?

[v-lili6-msft]

hi @Shelley 

Please try to create two measure as below:

Entitlement Qty = 
VAR _nextlinenumber=CALCULATE(MIN('Table'[Line Item Number]),FILTER(ALLEXCEPT('Table','Table'[Contract Number]),'Table'[Line Item Number]>MAX('Table'[Line Item Number]))) 
return
CALCULATE(SUM('Table'[Line Item Target Quantity]),FILTER(ALLEXCEPT('Table','Table'[Contract Number]),'Table'[Line Item Number]=_nextlinenumber))

Per Unit = DIVIDE(CALCULATE(SUM('Table'[Line Item Target Value])) ,[Entitlement Qty],0)

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Shelley]

@Greg_Deckler Thank you! This appears to work, but our data is sketchy and so I need to make sure this is robust. Is there any chance, you could please explain what each expression is doing? I try to use the Microsoft reference docs, but they are usually impossible to understand in regards to application.

[Shelley]

@Greg_Deckler Thank you! Your suggestion seems to work for Item #1, but how do I do Item # 2? That is, how do I select the next Line Item Number in sequence (110) and then grab the Line Item Quantity from that row and only that row?

[Greg_Deckler]

@Shelley - Seems like a measure with some straight-up FILTERs, like:

Measure = 
  VAR __Contract = MAX('Table'[Contract Number])
RETURN
  MAXX(FILTER('Table',[Contract Number] = __Contract && [Main/Sub Indicator] = "M" && [Material ID] = "PN-C01522"),[Line Item Target Value])