Multiplication between 2 tables in PowerPivot

[wethernt]

Hello, I was hoping someone can help me create a formula in PowerPivot. I have 2 tables:

Table one (lookup table): Country
Foreign Exchange Rate

Table two (facts): Customer
Country
Sales Local Currency

I would like to create a measure for Sales USD by multiplying the Sales Local Currency in Table two, by the Foreign Exchange Rate in Table one. Not sure how to go about this, nor proper syntax. Calculated column? Create a calculation? What function to use? Thanks for any assistance.

Tom in Atlanta GA USA.

 

[wethernt]

Interesting.... I used something called "Related" and I believe I did what I need.

=[Gross Sales Loc]*RELATED('Curr Fx Constant'[Fx Rate])

 

[Matt Allington]

Hi Tom from ATL GA. I have spent a lot of time in your home town over the years and I quite like the place.

You say you want to write a measure, but this doesn't look like a measure to me - it looks like a calc column. You may like to read my knowledge base article about the difference and why you should use a measure. Calculated Columns vs Measures in DAX - Excelerator BI

the correct measure (when you are ready) is

=sumx('Current FX Constant',calculate(sum(factTable[sales column])))