Forum Discussion
find the double interpolation value
Hi All,
i have a requirement to find the double interpolation value.
i was able to get the logic in excel but couldn't get the same answer in powerbi
there is Fact table, which has products, X, Y and we need to find Z ( double interpolation value)
| Fact | |||
| Product | X | Y | Z |
| A | 3.5 | 4.00% | ? |
| B | 2.2 | 2.80% | ? |
| C | 5 | 0.50% | ? |
there is a lookup table which has all the fixed X and Y ranges
Note - there would be different similar lookup tables for different products
| Lookup | ||||
| Y | ||||
| 0 | 2.38% | 4.76% | ||
| X | 1 | 750 | 700 | 650 |
| 2 | 600 | 570 | 550 | |
| 3 | 520 | 470 | 430 | |
| 4 | 400 | 350 | 300 | |
| 5 | 250 | 200 | 100 |
FYI - below is the interploation formula
yt=y1+(y2-y1)*(xt-x1/x2-x1)
below is one of the example explaied
for Product A, X=3.5 and Y=4%, need to find Z
if we look at the lookup table X and Y resides on somewhere marked yellow cells (between 3 to 4 and 2.38% to 4.76).
Z value from product A would be between 300 to 470
| Lookup | ||||
| Y | ||||
| 0 | 2.38% | 4.76% | ||
| X | 1 | 750 | 700 | 650 |
| 2 | 600 | 570 | 550 | |
| 3 | 520 | 470 | 430 | |
| 4 | 400 | 350 | 300 | |
| 5 | 250 | 200 | 100 |
final value can be derived in 2 steps
1. find X adjustments 410 (between 470 and 350) & 365 (between 430 and 300)
2. find Y adj which is our final value 379
below is the interploation formula
yt=y1+(y2-y1)*(xt-x1/x2-x1)
| 2.38% | 4.00% | 4.76% | |
| 3 | 470 | 430 | |
| 3.5 | 410 | 379 | 365 |
| 4 | 350 | 300 |
| Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | ||
| y1 | y2-y1 | xt-x1 | x2-x1 | xt-x1/x2-x1 | Col2*Col5 | Col1+Col6 | ||
| X adj | 470 | -120 | 0.50 | 1.00 | 0.5 | -60 | 410 | y2=350,xt=3.5,x1=3,x2=4 |
| X adj | 430 | -130 | 0.50 | 1.00 | 0.5 | -65 | 365 | y2=300,xt=3.5,x1=3,x2=4 |
| Y adj | 410 | -45 | 0.016 | 0.024 | 0.680 | -31 | 379 | y2=365,xt=4.00%,x1=2.38%,x2=4.76% |
simirarly need to find the Z values for all the records.
ex. Z value for Product B
similarly look up table is different for product B
and below is the value for product B which is 493
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 |
| 2.06% | 2.80% | 4.12% | |
| 2 | 520 | 510 | |
| 2.2 | 500 | 493 | 480 |
| 3 | 420 | 360 |
| Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | |
| y1 | y2-y1 | xt-x1 | x2-x1 | xt-x1/x2-x1 | Col2*Col5 | Col1+Col6 | |
| X adj | 520 | -100 | 0.20 | 1.00 | 0.2 | -20 | 500 |
| X adj | 510 | -150 | 0.20 | 1.00 | 0.2 | -30 | 480 |
| Y adj | 500 | -20 | 0.007 | 0.021 | 0.358 | -7 | 493 |
can you please help me to get the solution
you can simply add a product column to the lookup table and then use the product filter during the TREATAS lookup.
11 Replies
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?
