# Is this possible to create a calculated column like that???

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

• 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])
)```

Jacob

• Edited by Tuesday, April 9, 2013 11:45 PM Typo
• Marked as answer by 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])
)```