cancel
Showing results 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

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

1 ACCEPTED SOLUTION
Super User

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

11 REPLIES 11
Super User

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

Helper I

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
Super User

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?

Helper I

@lbendlin  yes this table presentation is fine.

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

Helper I

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

Super User

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

Helper I

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
Super User

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

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

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

Super User

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

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

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors