Currency conversion can be a PITA. As you may know there are three standard requirements that pop up:
- One to Many. Where the database is in one currency and it needs to be reported in many currencies.
- Many to One. Where different rows are in different currencies and need to be converted into a standard currency.
- Many to Many currency. Different rows are in different currencies and need to be reported in many currencies.
This is slightly difficult in PowerPivot due to the lack of “Many to Many” support” and also no support for “default members” to have a default currency.
In this example we have the following tables (example PowerPivot Workbook attached). One initial solution is to use a “calculated member in the PowerPivot Window to convert to a standard currency (EUR) and then apply a one to many relationship for the reporting currency. For a lot of customers that won;t work as this results in a double conversion and therefore errors in the maths.
My solution is not elegant. In fact, maybe it will appear in the sqlcat site of “worst practices”, but it works like a dream so far…
The Solution
1. First add a sheet for your FromCurrency and your ToCurrency. Its quiet common that your source currency may be in 10-20 currencies but you only want 2-3 reporting currencies
2. Decide on your “default” currency. in my case EUR
3. do NOT add a relationship between the Facts and the “To Currency”, when a To currency is selected we need to use a calculated measure to do the conversion dynamically as many to many relationships are not supported by default in PowerPivot.
4. Add a slicer for the “To Currency”
5. Add a calculated measure for the Amount.
=if(countrows(ToCurrency)=1,sumx(FactTrans,sumx(filter(ExchangeRates,ExchangeRates[FromCurrency]=FactTrans[Currency]),FactTrans[Amount]/ ExchangeRates[MidRate])),sumx(FactTrans,sumx(filter(filter(ExchangeRates,ExchangeRates[FromCurrency]=FactTrans[Currency]),ExchangeRates[ToCurrency]=”EUR”),FactTrans[Amount] / ExchangeRates[MidRate])))
How does it Work?
The trick is the “sumx” function effectively iterates through every row, finding the related conversion rate and applying the conversion and summing the results. The IF statement detects of we have not selected only one currency and if so, applies a default one.
How does it compare to Analysis Services Currency Conversion?
The formula looks lot more complex than the SSAS MDX equivalent technique, and SSAS has the benefit of also being able to do formatting with “Locales” to change the currency symbols dynamically too. But its quick and easy-ish and it works…
