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