March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
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.
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 |
you will need to describe your scenario in more detail. Measures are designed differently, you cannot easily convert the DAX for a calculated column to the DAX for a measure (especially if the business purpose is unknown).
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).
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
86 | |
73 | |
58 | |
52 |
User | Count |
---|---|
197 | |
133 | |
107 | |
69 | |
65 |