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

  • 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

Charter

New Member
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.
 

Attachments

  • Lookup1.xlsx
    155.9 KB · Views: 18
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.
upload_2017-3-28_15-24-37.png

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:
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?
 
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.
 
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.
 
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.
 
Thanks, this is what I have tried but only getting errors - hopefully an error on my part rather than not being possible?

Code:
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]))
 
Syntax is off. You need to declare variable.
Code:
[NewColumnName]=
VAR
    variablename = Table1
RETURN
.....
 
Sorry Chihiro, I'm still stuck..... I know I'm making a daft mistake but not sure where....... this is what I'm trying:
 

Attachments

  • error.PNG
    error.PNG
    12.1 KB · Views: 8
Measure is bit different from Calculated column. I'm busy right now. I'll see if I can whip up a sample later.
 
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 :)
 
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).
 
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?
 
If it's lookup operation. In most cases it's far more efficient to do it at PowerQuery stage or ealier (in SQL side).
 
Back
Top