PDA

View Full Version : How to calculate values between two different tables



EdwinPWP
2012-01-05, 05:03 PM
I have two types of Tables:
PriceFact Table
Data Table

In the PriceFact Table is a list with item id's imported into PowerPivot.

The Data Table is a link table into Powerpivot.

A relationship was built based on Item ID which both tables have using Powerpivot.

In my Data table, every row is a purchase and one of the column name is called "Item Qty Purchased" which states how many of this item was purchased in this line.

The Price Fact table tells me how much this unit costs depending on the Item ID.

I want to create a calculated column where we multiply "Item Qty Purchsed" by the "Unit Cost".

In Excel its more like:
=A5*Vlookup(A1,PriceFactsheet!A1:B245,2,0)

Where we multiple based on the vlookup.

Is there a DAX I can use in Powerpivot to do this?

JoePublic
2012-01-06, 11:31 AM
It should just be something like:
=[Item Qty Purchased]*Related(PriceFact[Unit cost])