1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui...

3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

# Interpolation formula in Power Pivot

Discussion in 'Power Pivot, Power Map etc' started by Charter, Mar 28, 2017.

1. ### CharterNew Member

Messages:
20
Hi,

I'm slowly getting up to speed with Power Pivot but keep running into things I just don't know how to do !! Would anyone know how I might complete the attached sheet in Power Pivot - I'm basically looking up a value from DATA to give an interpolated result D in DATA using a 'LOOKUP' table to find the interpolated answer - the calculation is shown in the highlighted area. Not sure even how to begin this in Power Pivot so any pointers much apprecaited - this is Excel 2016. Cheers.

File size:
155.9 KB
Views:
5
2. ### ChihiroExcel Ninja

Messages:
4,057
You can do it in single column... but it's going to be pain and hard to read.

Do it in steps.

1st Column: MIN of B when >=[A]. Named it [B_A]
DAX =CALCULATE(MIN('Query 1'["B"]),FILTER('Query 1','Query 1'["B"]>=Query[A]))

2nd Column: MAX of B when <=[A]. Named it [B_B]
DAX =CALCULATE(MAX('Query 1'["B"]),FILTER('Query 1','Query 1'["B"]<=Query[A]))

3rd Column: MAX of [C] when >=[A]. Named it [C_A]
DAX =CALCULATE(MAX('Query 1'[C]),FILTER('Query 1','Query 1'["B"]>=Query[A]))

4th column: MIN of [C] when <=[A]. Named it [C_B]
DAX =CALCULATE(MIN('Query 1'[C]),FILTER('Query 1','Query 1'["B"]<=Query[A]))

5th column: named [Interpolated]
=([A]-[B_B])/([B_A]-[B_B])*([C_A]-[C_B])+[C_B]

See image below for example.

EDIT: 'Query 1' is the lookup table. Query is the data table.
EDIT2: Remove "" from ["B"]. I had to add it to avoid it being interpreted as forum code for bold text.
Last edited: Mar 28, 2017
Charter and Thomas Kuriakose like this.
3. ### CharterNew Member

Messages:
20
Fantastic Chihiro, many thanks - it works well and like you say, much easier to read when split into seperate columns. I have it working in my test file, I'm now trying to set the calculated columns up in the LookUp table rather than the DATA table but I'm getting errors - what would I need to change in DAX to resolve this - I've tried RELATED with no joy?
4. ### CharterNew Member

Messages:
20
Hmmm, sorry - that turns out to be a daft question! Of course I need to do this in the DATA table All good now.
Chihiro likes this.
5. ### CharterNew Member

Messages:
20
Hi Chihiro,

As a matter of interest and to learn some more may I ask a couple more questions please?

How would the code look if it was combined into a single column?

Can I use a string variable in measures to define the table name and refer to this variable in the code to name the table?

Many thanks.
6. ### ChihiroExcel Ninja

Messages:
4,057
For single column calculation. Just replace respective column reference in the final calculation with intermediate steps.

Ex:
=([A]-CALCULATE(MAX('Query 1'["B"]),FILTER('Query 1','Query 1'["B"]<=Query[A])))/(CALCULATE(MIN('Query 1'["B"]),FILTER('Query 1','Query 1'["B"]>=Query[A]))-CALCULATE(MAX('Query 1'["B"]),FILTER('Query 1','Query 1'["B"]<=Query[A])))*(CALCULATE(MAX('Query 1'[C]),FILTER('Query 1','Query 1'["B"]>=Query[A]))-CALCULATE(MIN('Query 1'[C]),FILTER('Query 1','Query 1'["B"]<=Query[A])))+CALCULATE(MIN('Query 1'[C]),FILTER('Query 1','Query 1'["B"]<=Query[A]))

As for the second question...
There is no point in assigning variable in this instance. Just name/rename each table to the name you want and reference it in the formula.
Thomas Kuriakose likes this.
7. ### CharterNew Member

Messages:
20
Hi Chihiro,

Many thanks, makes sense

Would you be up for showing me how to assign the table name via a variable - for learning but also it might be that I have multiple lookup tables and need to swap between them with relative ease.

Cheers.
8. ### ChihiroExcel Ninja

Messages:
4,057
It's normally used like below. I've never seen table as whole used as variable. It's usually a column or aggregated column/calculation.
Code (vb):
Measure:=
VAR
VariableName = SUM(Query1[Column1])
RETURN
IF(
AND([DateCol]<Today(),[DateCol]>=Today()-7),
DIVIDE(
VariableName,
7)
)
See link for detail on variable use in DAX.
http://blog.learningtree.com/excel-2016-dax-variables/
https://blog.crossjoin.co.uk/2016/10/13/defining-variables-in-dax-queries/
9. ### CharterNew Member

Messages:
20
Thanks, this is what I have tried but only getting errors - hopefully an error on my part rather than not being possible?

Code (vb):
Ref1=:"Table1" 'name of lookup table in quotes as otherwise throws an error.

=([A]-CALCULATE(MAX('Ref1'["B"]),FILTER('Ref1','Ref1'["B"]<=Query[A])))/(CALCULATE(MIN('Ref1'["B"]),FILTER('Ref1','Ref1'["B"]>=Query[A]))-CALCULATE(MAX('Ref1'["B"]),FILTER('Ref1','Ref1'["B"]<=Query[A])))*(CALCULATE(MAX('Ref1'[C]),FILTER('Ref1','Ref1'["B"]>=Query[A]))-CALCULATE(MIN('Ref1'[C]),FILTER('Ref1','Ref1'["B"]<=Query[A])))+CALCULATE(MIN('Ref1'[C]),FILTER('Ref1','Ref1'["B"]<=Query[A]))
10. ### ChihiroExcel Ninja

Messages:
4,057
Syntax is off. You need to declare variable.
Code (vb):
[NewColumnName]=
VAR
variablename = Table1
RETURN
.....
11. ### CharterNew Member

Messages:
20
Sorry Chihiro, I'm still stuck..... I know I'm making a daft mistake but not sure where....... this is what I'm trying:

File size:
12.1 KB
Views:
4
12. ### ChihiroExcel Ninja

Messages:
4,057
Measure is bit different from Calculated column. I'm busy right now. I'll see if I can whip up a sample later.
13. ### CharterNew Member

Messages:
20
Very kind - thank you.
14. ### CharterNew Member

Messages:
20
Hello again, just to confirm that I would like to define the varialbe as a Measure and reference this variable in a calculated column - hopefully possible
15. ### ChihiroExcel Ninja

Messages:
4,057
Ah, as I suspected. You can't use entire table as variable.

Variable must be single column aggregated or otherwise, or some expression that returns scalar value.

I'm not sure I see the point in making table into variable. Since similar tables can have relationships built (be it through key column or dimension table).
16. ### CharterNew Member

Messages:
20
Hi Chihiro,

Thanks for taking a look at this, much appreciated. Basically what I'm hoping to achieve is an efficient way to swap between lookup tables without having to edit each instance of the calculated column lookup code each time. To set the scene, I have 8 calculated columns which use the 'long code' (to avoid having 40 columns) to find an interpolated value from one of a few lookup tables. Which lookup table is referenced changes from time to time so I have been hoping to find a way to efficienty switch which lookup table is referenced by each column.

Hopefully this makes more sense?
17. ### ChihiroExcel Ninja

Messages:
4,057
If it's lookup operation. In most cases it's far more efficient to do it at PowerQuery stage or ealier (in SQL side).
18. ### CharterNew Member

Messages:
20
Thanks again Chihiro - I need to learn Power Query I think Cheers.