find the double interpolation value

[arsene49]

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

[lbendlin]

Your lookup table is not in a usable format.  Please show how you use it in Excel.

[arsene49]

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

[lbendlin]

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?

[arsene49]

@lbendlin  yes this table presentation is fine.

calculated column is prefered because this value is again used for few other row level calculation.

[arsene49]

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

[lbendlin]

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

[arsene49]

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

[lbendlin · Accepted solution]

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

[arsene49]

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

[lbendlin]

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

[lbendlin]

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