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.
Your lookup table is not in a usable format. Please show how you use it in Excel.
Thanks Ibendlin for responding.
the lookup table given is in matrix format with X on the rows and Y on the columns
This is how they have given and as of now i have calculated manually in excel for each record using the formula explained which i need to automate in powerbi.
| Y | Y | Y | ||
| 0 | 2.38% | 4.76% | ||
| X | 1 | 750 | 700 | 650 |
| X | 2 | 600 | 570 | 550 |
| X | 3 | 520 | 470 | 430 |
| X | 4 | 400 | 350 | 300 |
| X | 5 | 250 | 200 | 100 |
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.