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

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

    Attached Files:

  2. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,318
    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: Mar 28, 2017
    Charter and Thomas Kuriakose like this.
  3. Charter

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

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

    Charter New 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. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,318
    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. Charter

    Charter New 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. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,318
    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. Charter

    Charter New 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. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,318
    Syntax is off. You need to declare variable.
    Code (vb):
    [NewColumnName]=
    VAR
        variablename = Table1
    RETURN
    .....
  11. Charter

    Charter New 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:

    Attached Files:

  12. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,318
    Measure is bit different from Calculated column. I'm busy right now. I'll see if I can whip up a sample later.
  13. Charter

    Charter New Member

    Messages:
    20
    Very kind - thank you.
  14. Charter

    Charter New 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. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,318
    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. Charter

    Charter New 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. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,318
    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. Charter

    Charter New Member

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

Share This Page