Forum Discussion
Find row from Table based on value from Measure
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 f...
Greg_Deckler
Community Champion
Community ChampionNo sample data so will wing it. 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
Perhaps:
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])
Thanks for the reply. Not able to attached the sample XL file.
Should I copy and paste the table data in the body?
Result table and requirements (Table PBI_CUM below)
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. Table PBI_CUM
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