Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
arsene49
Helper I
Helper I

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   
ProductXYZ
A3.54.00%?
B2.22.80%?
C50.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  
  02.38%4.76%
X1750700650
 2600570550
 3520470430
 4400350300
 5250200100

 

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  
  02.38%4.76%
X1750700650
 2600570550
 3520470430
 4400350300
 5250200100

 

 

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%
3470 430
3.5410379365
4350 300

 

 Col1Col2Col3Col4Col5Col6Col7 
 y1y2-y1xt-x1x2-x1xt-x1/x2-x1Col2*Col5Col1+Col6
X adj470-1200.501.000.5-60410y2=350,xt=3.5,x1=3,x2=4
X adj430-1300.501.000.5-65365y2=300,xt=3.5,x1=3,x2=4
         
Y adj410-450.0160.0240.680-31379y2=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%
1650600580
2550520510
3450420360
4330300250
5200150100

 

 2.06%2.80%4.12%
2520 510
2.2500493480
3420 360

 

 Col1Col2Col3Col4Col5Col6Col7
 y1y2-y1xt-x1x2-x1xt-x1/x2-x1Col2*Col5Col1+Col6
X adj520-1000.201.000.2-20500
X adj510-1500.201.000.2-30480
        
Y adj500-200.0070.0210.358-7493

 

can you please help me to get the solution

 

1 ACCEPTED SOLUTION

you can simply add a product column to the lookup table and then use the product filter during the TREATAS lookup.

View solution in original post

11 REPLIES 11
lbendlin
Super User
Super User

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.

 

  YYY
  02.38%4.76%
X1750700650
X2600570550
X3520470430
X4400350300
X5250200100

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.

Here is my solution proposal based on calculated columns. Please verify.

 

lbendlin_0-1692839828717.png

 

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%
1650600580
2550520510
3450420360
4330300250
5200150100

 

Lookup for Product C

 0%3.15%9.46%
1150014001300
2120011001000
3900800700
4600500400
5300200100

 

Lookup for Product D

 0%2.52%5.04%
1750700650
2600550500
3450400350
4300250200
515010050

you can simply add a product column to the lookup table and then use the product filter during the TREATAS lookup.

Thank you @ibendlin. this solution works perfect. if I have to do it via measure then same formula, logics and functions should work right?

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors