Forum Discussion
find the double interpolation value
you can simply add a product column to the lookup table and then use the product filter during the TREATAS lookup.
Is this an accurate representation?
| x | y | value |
| 1 | 0 | 750 |
| 2 | 0 | 600 |
| 3 | 0 | 520 |
| 4 | 0 | 400 |
| 5 | 0 | 250 |
| 1 | 0.0238 | 700 |
| 2 | 0.0238 | 570 |
| 3 | 0.0238 | 470 |
| 4 | 0.0238 | 350 |
| 5 | 0.0238 | 200 |
| 1 | 0.0476 | 650 |
| 2 | 0.0476 | 550 |
| 3 | 0.0476 | 430 |
| 4 | 0.0476 | 300 |
| 5 | 0.0476 | 100 |
Do you need the Z value as a measure or a calculated column?
lbendlin yes this table presentation is fine.
calculated column is prefered because this value is again used for few other row level calculation.
lbendlin each product has different lookup tables.
In each lookup, X values are ranges same between 1 to 5 but Y range varies between different product lookup. hence shown example for Product B also in the original message.
If X/Y is outside of this loookup matrix, please take the edge point.
I was just about to ask about that. There is another special scenario when the fact value matches the lookup (like for C and X).
Here is my solution proposal based on calculated columns. Please verify.
These values are perfect. thank you lbendlin
but in my case each product has different lookup tables.
In each lookup, X values are ranges same between 1 to 5 (1,2,3,4,5) but Y range varies between different product lookup.
shown example for Product B in the original message.have added lookup's of other products here -
Lookup for Product B
0% 2.06% 4.12% 1 650 600 580 2 550 520 510 3 450 420 360 4 330 300 250 5 200 150 100 Lookup for Product C
0% 3.15% 9.46% 1 1500 1400 1300 2 1200 1100 1000 3 900 800 700 4 600 500 400 5 300 200 100 Lookup for Product D
0% 2.52% 5.04% 1 750 700 650 2 600 550 500 3 450 400 350 4 300 250 200 5 150 100 50