Powerpivot -> Measures for Multiple currency mapping between two tables

[kharemanas]

Hi,
I have two tables - one having data with sales for multiple countries and currency combination and another having the USD conversion rate for each currency. I would like my pivot to have all the figures in USD.

Using normal excel, i'd vlookup the conversion factor into my main table and then create a USD column by multiplying the conversion factor with the sales figures.

I believe this can be achieved through powerpivot as well and it would give me the flexibility later as I have conversion factor for 2014 and 2015, which I would want to have the flexibility to chose from.

However, I tried it and am unable to get it working right.

Any ideas how to go about it in powerpivot?
regs
Manas

 

[theBardd]

You could just add a calculated column to your Sales table that calculates the USD equivalent of teh amount, something like

USDAmount: =[Amount]/RELATED(Currency[Rate])

Depending upon your tables, you might need to create a composite date/currency calculated columns on both the Sales and Currency tables, and then join on those composite keys, such as this

CcyKey: =[Ccy]&FORMAT([Date],"yyyymmdd")

You can then just aggregate pull USDAmount into your pivot.

 

[kharemanas]

Great. Works fine. Thanks so much.

Just out of curiosity - is there a way to do it via calculated formula in powerpivot rather than adding a column in the base data?

 

[theBardd]

I don't think it is the best way, but assuming the same layout as I used above, you could use

USDAmount:=IF( HASONEVALUE( Sales[CcyKey] ),
    SUMX(
         Sales,
         Sales[Amount] / CALCULATE( 
                                       MIN( 
                                           Currency[Rate] 
                                        ) 
                                    )
     ),
     BLANK()
 )

 

[theBardd]

You probably don't need the IF(HASONEVALUE(... if your keys enfoirce a 1-1 joiun and you don't show the exchange rate in the query, so you could just use

USDAmount:=SUMX(
     Sales,
     Sales[Amount] * CALCULATE( 
                         MIN( 
                              Currency[Rate] 
                             ) 
                     )
 )