Find row from Table based on value from Measure

Question

I have a measure that calculates remaining units to produce for different items.

I also have a table that has quantities and unit prices for each item. The table has row number that starts with 1 for each item, quantity and price for that item.

Now I want to get the row that has quantity nearest to the value returned by the measure (absolute value) and then get the price value passed to another measure.

For example, if the remaining quantity to produce is 2500 tons, and the table has records for 2490 and 2512 tons and corresponding price, I would like to get price from the row corresponding to 2490.

Any help appreciated

greg_deckler · Answer

No sample data so will wing it.&nbsp;Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
&nbsp;
Perhaps:
&nbsp;
Measure =
  VAR __Table = 
    ADDCOLUMNS(
      ADDCOLUMNS(
          'Table',
          "__Measure",[I have a measure]
      ),
      "__Diff",ABS([Price] - [__Measure])
    )
  VAR __Min = MINX(__Table,[__Diff])
RETURN
  MINX(FILTER(__Table,[__Diff] = __Min),[Price])

chalapathy · Answer

Greg_Deckler&nbsp;Thanks for the reply. Not able to attached the sample XL file.&nbsp;&nbsp;Should I copy and paste the table data in the body?&nbsp;

chalapathy · Answer

&nbsp;Result table and requirements (Table PBI_CUM below)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Reqd:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 43,541.33&nbsp;DISPLAY IN APP&nbsp;&nbsp;&nbsp;&nbsp;(Computed measure)&nbsp;Measure%usetons_reqdAVPRICEValue&nbsp;&nbsp;&nbsp;&nbsp;(% of Reqd --- user input)reqd*%useFrom TablePBI_CUMtons_reqd*avprice&nbsp;&nbsp;&nbsp;Item115%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,531.20335.7918572193123.776&nbsp;&nbsp;&nbsp;Item21.18%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 513.79326.232921167614.473&nbsp;&nbsp;&nbsp;Item3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,044.99&nbsp;2360738.249&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AVCOST&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 335.09&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table PBI_CUM have Cum_total and corresponding Value and AVG_PRICE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Objective is to find the row having cum_total that is closest to the measure ofeach item and then return the corresponding AVPriceThen Calculate AV.Cost based combination of items and their prices.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Item Row No Starts with 1 when the item number chanes and increments by 1 for each record&nbsp;&nbsp;&nbsp;There are 13 such items&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Additional Help:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;User needs to have the ability to adjust the %use for each item from the app and get the AVCOST.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I am using 13 what-ifs but not able to display the values in tabular form.&nbsp;&nbsp;&nbsp;&nbsp;Table PBI_CUMITEMNMBRItem Row Nocum_totalAVPRICEItem114887.3839339.48987Item124887.3839339.48987Item134906.1428339.42807Item144906.1428339.42807Item156351.6116336.1446Item166424.0669336.24448Item176444.3884336.17749Item186464.2634336.11238Item196480.5848336.00884Item1106498.4419335.89615Item1116519.433335.85111Item1126519.433335.85111Item1136538.0134335.79186Item1146558.4866335.74946Item1156617.5134335.50486Item1166628.9062335.42018Item1176628.9062335.42018Item1186650.683335.47277Item1197017.0669335.97126Item1207101.1294336.07838Item2192.99107316.92118Item22112.0625317.44518Item23132.5307.04211Item24151.29464308.03067Item25234.10714317.57075Item26315.98214322.08691Item27334.15178321.97342Item28351.94642321.87362Item29440.60713324.51497Item210526.94642326.23292Item211611.76785327.44848Item212630.33928327.52365Item213649.75892327.59766

Forum Discussion

Find row from Table based on value from Measure

3 Replies

Related content

Filtering table based on measure

Filter based on Measures

Measure filter for a table based on 2 slicers

finding the nth record based on a measure

Filtering Table Based On Measure Value

					Reqd:	43,541.33
DISPLAY IN APP					(Computed measure)
Measure	%use	tons_reqd	AVPRICE	Value
	(% of Reqd --- user input)	reqd*%use	From Table PBI_CUM	tons_reqd*avprice
Item1	15%	6,531.20	335.791857	2193123.776
Item2	1.18%	513.79	326.232921	167614.473
Item3
Item4
Item5
Item6
Item7
Item8
Item9
Item10
Item11
Item12
Item13
Total		7,044.99		2360738.249

	AVCOST	335.09
Table PBI_CUM have Cum_total and corresponding Value and AVG_PRICE

Objective is to find the row having cum_total that is closest to the measure ofeach item and then return the corresponding AVPrice
Then Calculate AV.Cost based combination of items and their prices.

Item Row No Starts with 1 when the item number chanes and increments by 1 for each record
There are 13 such items

Additional Help:
User needs to have the ability to adjust the %use for each item from the app and get the AVCOST.

I am using 13 what-ifs but not able to display the values in tabular form.

ITEMNMBR	Item Row No	cum_total	AVPRICE
Item1	1	4887.3839	339.48987
Item1	2	4887.3839	339.48987
Item1	3	4906.1428	339.42807
Item1	4	4906.1428	339.42807
Item1	5	6351.6116	336.1446
Item1	6	6424.0669	336.24448
Item1	7	6444.3884	336.17749
Item1	8	6464.2634	336.11238
Item1	9	6480.5848	336.00884
Item1	10	6498.4419	335.89615
Item1	11	6519.433	335.85111
Item1	12	6519.433	335.85111
Item1	13	6538.0134	335.79186
Item1	14	6558.4866	335.74946
Item1	15	6617.5134	335.50486
Item1	16	6628.9062	335.42018
Item1	17	6628.9062	335.42018
Item1	18	6650.683	335.47277
Item1	19	7017.0669	335.97126
Item1	20	7101.1294	336.07838
Item2	1	92.99107	316.92118
Item2	2	112.0625	317.44518
Item2	3	132.5	307.04211
Item2	4	151.29464	308.03067
Item2	5	234.10714	317.57075
Item2	6	315.98214	322.08691
Item2	7	334.15178	321.97342
Item2	8	351.94642	321.87362
Item2	9	440.60713	324.51497
Item2	10	526.94642	326.23292
Item2	11	611.76785	327.44848
Item2	12	630.33928	327.52365
Item2	13	649.75892	327.59766