Using exchange rates in measures

[KimC2504]

Hi All,

I am building my first Power Pivot model, so far so good but I am stuck on a measure which I am hoping someone may have a solution for. I currently have 11 tables but I will give an example of 2 below. Basically I want to be able to multiply a simple measure by the exchange rate. First table is Actual data and second table is exchange rates. The first measure is Actuals = sum(FY14[USD])/1000. The second measure I am trying to use is Actuals Exch = [Actuals]*(Exchange Rates[Ratio]). The second measure doesn't work as I get a calculation measure. These tables are all linked via country and date tables. Any suggestions ??
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Country[/TD]
[TD]Type[/TD]
[TD]Channel[/TD]
[TD]USD[/TD]
[/TR]
[TR]
[TD]201402[/TD]
[TD]Aust[/TD]
[TD]Dog[/TD]
[TD]Direct[/TD]
[TD]136,429[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]NZ[/TD]
[TD]Cat[/TD]
[TD]Direct[/TD]
[TD]38,209[/TD]
[/TR]
[TR]
[TD]201404[/TD]
[TD]NZ[/TD]
[TD]Dog[/TD]
[TD]Indirect[/TD]
[TD]200,000[/TD]
[/TR]
[TR]
[TD]201402[/TD]
[TD]Aust[/TD]
[TD]Cat[/TD]
[TD]Direct[/TD]
[TD]500,000[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]NZ[/TD]
[TD]Horse[/TD]
[TD]Direct[/TD]
[TD]1,000,000[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]Aust[/TD]
[TD]Mouse[/TD]
[TD]Indirect[/TD]
[TD]200,000[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]NZ[/TD]
[TD]Dog[/TD]
[TD]Direct[/TD]
[TD]100,000[/TD]
[/TR]
[TR]
[TD]201402[/TD]
[TD]Aust[/TD]
[TD]Cat[/TD]
[TD]Indirect[/TD]
[TD]50,000[/TD]
[/TR]
[TR]
[TD]201404[/TD]
[TD]NZ[/TD]
[TD]Cat[/TD]
[TD]Indirect[/TD]
[TD]600,000[/TD]
[/TR]
</tbody>[/TABLE]

Exchange rates[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Country[/TD]
[TD]Ratio[/TD]
[/TR]
[TR]
[TD]201402[/TD]
[TD]Aust[/TD]
[TD]0.95[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]Aust[/TD]
[TD]0.92[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]NZ[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]201404[/TD]
[TD]NZ[/TD]
[TD]0.85[/TD]
[/TR]
</tbody>[/TABLE]

 

[cnort21]

Does the exchange rates table need to be linked to the country and date tables?

If not: It might be easier to create a "Date/Country" Combo on these two tables and connect them directly. Then use the formula Actuals Exch = [Actuals]*related(Exchange Rates[Ratio]).

If so: Use the following formula, Actuals Exch = [Actuals]*lookupvalue('Exchange Rates'[Ratio],'Exchange Rates'[Date],[Date],'Exchange Rates'[Country],[Country]).

 

[Jacob Barnett]

Kim, you are on the right path and it sounds like your model is perfectly set up but you can't just use a 'naked' column in a formula, it needs to be packaged in someway!

This is pretty straightforward for the granular stuff something like this should work:

[Actuals Exch] = [Actuals] * MAX(Exchange Rates[Ratio])

The problem is that this will give you an 'incorrect' total as it will then use the highest rate (some people would recommend you use Values() instead of Max() here as its less risky but it wouldn't have helped get to the right answer as it would 'error').

What you will need to do is write an iterative formula like this:

[Actuals Exch Iterated] = SUMX(SUMMARIZE(FY14, FY14[Date],FY14[Country]), [Actuals Exch] )

What this does is iterate your formula over a grouped table of dates and countries and add up the entries in the total. Note:

- For performance reasons you may only want to do this for totals, in which case look into HASONEVALUE().
- I've written this on the fly and its untested, but should work

 

[KimC2504]

Thank you Jacob Barnett & cnort21 for two great solutions to my post. I ended up going with the lookupvalue solution and it works perfectly It is so exciting getting my power pivot model working. Thanks again for your help.