locked
Is this possible to create a calculated column like that??? RRS feed

  • Question

  • My powerpivot has 2 tables, and I would like to create a calculated column in table 1 that can look up a value in table 2

    ex: the answer should be Price = $120

    1st Table:

    Customer Item Price level QTY
    Jack Bike B 12

    2nd Table:

    Item Price level TO QTY FROM QTY Price
    Bike A 9 1 $150
    Bike A 9999 10 $120
    Bike B 9 1 $180
    Bike B 9999 10 $150
    Car A 9999 1 $1,500


    Tuesday, April 9, 2013 11:27 PM

Answers

  • Johnny, try this:

    =CALCULATE(
               MAX(Table2[Price]),
               ALL(Table2), Table2[Item] = EARLIER(Table1[Item]), 
               Table2[FROM QTY]<=EARLIER(Table1[QTY]),
               Table2[TO QTY]>EARLIER(Table1[QTY])
               )

    Ps based on your data the answer should be $150 :-)

    Jacob


    • Edited by Jacob Barnett Tuesday, April 9, 2013 11:45 PM Typo
    • Marked as answer by Johnny_yang Thursday, April 11, 2013 9:17 PM
    Tuesday, April 9, 2013 11:44 PM

All replies

  • Johnny, try this:

    =CALCULATE(
               MAX(Table2[Price]),
               ALL(Table2), Table2[Item] = EARLIER(Table1[Item]), 
               Table2[FROM QTY]<=EARLIER(Table1[QTY]),
               Table2[TO QTY]>EARLIER(Table1[QTY])
               )

    Ps based on your data the answer should be $150 :-)

    Jacob


    • Edited by Jacob Barnett Tuesday, April 9, 2013 11:45 PM Typo
    • Marked as answer by Johnny_yang Thursday, April 11, 2013 9:17 PM
    Tuesday, April 9, 2013 11:44 PM
  • Works great! thank you!
    Thursday, April 11, 2013 9:17 PM