# Powerpivot Currency Converter - multiple Report Currencys



## Ugur Canbaz (Dec 18, 2012)

Hi at all.

can you please help me to find a one formula instead of the following calculated columns that i have?

AmountUSD = [TransactionAmount]/CALCULATE(VALUES(factExchangeRate[AverageRate]))

AmountGBP = [AmountUSD]* CALCULATE(VALUES(factExchangeRate[AverageRate]); (FactExchangeRate[ToCurrencyKey] =2);
FILTER(ALL(FactSale); FactSale[TransactionDate] = EARLIER(FactSale[TransactionDate])))

AmountZAR = [AmountUSD]* CALCULATE(VALUES(factExchangeRate[AverageRate]); (FactExchangeRate[ToCurrencyKey] =3);
FILTER(ALL(FactSale); FactSale[TransactionDate] = EARLIER(FactSale[TransactionDate])))

AmountEUR = [AmountUSD]* CALCULATE(VALUES(factExchangeRate[AverageRate]); (FactExchangeRate[ToCurrencyKey] =4);
FILTER(ALL(FactSale); FactSale[TransactionDate] = EARLIER(FactSale[TransactionDate])))

Thank you in advance!


----------



## Laurent C (Dec 18, 2012)

Assuming you have a Time dimension and a Currency dimension, with relationships from FactExchangeRate  to the Time and Currency tables, and your fact table is linked to these lookup tables as well, you could do something like that:
<code>
[Sum of Sales] :=IF(
     HASONEVALUE(Currency[CurrencyKey]);
       SUMX(
            FactSale;
            FactSale[TotalAmount] 
             *CALCULATE(MIN( FactExchangeRate[ExchangeRate] ))
       );
       SUM(FactSale[TotalAmount])
)
</code>
The IF(HASONEVALUE(... being there for the case where no unique selection has been made for the currency. I opted for USD as a default value, but this may change.
(Not fully tested, and since I had to rename a few things to make it look more like your schema,  some typos might remain)


----------



## Ugur Canbaz (Dec 18, 2012)

Hi Laurent,

thank you for your help.

I'am using the function Hasonevalue to präsentation my "calculated columns", it works fine. And my modell have relationships from FactExchangeRate to the Time and Currency tables too. But please think about that my incoming column [Transactionamount] (what you called as TotalAmount correct?) has different incoming sales currencys. Additionally my Currency table has multiple currencys in a part of time. 
I think the formula need a many-to-many solution. 
Can i send you my Excel 2013 with Powerpivot file to you?


----------



## Laurent C (Dec 18, 2012)

Indeed, I assumed all your data in the [Transactionamount] would be in dollars (that, is normalized). If you want to send me the Excel file, feel free to do it : lcouartou >> gmail.com


----------



## marco.russo (Dec 18, 2012)

Ugur,

the currency conversion calculation is a many-to-many pattern if you have transactions in multiple currencies and you want to report an arbitrary currency at query time.
If you want to report always the same currency (i.e. USD) then it might be better applying the currency conversion in a calculated column.
Can you show the data model you have?

Marco


----------



## Ugur Canbaz (Dec 18, 2012)

Hi Marco,

a few screenshots (3 pieces) from my model are to find here:
http://imageshack.us/g/20/<wbr>01diagramm.jpg/

Yes, what i want is exactly to report an arbitrary currency for my multiple currency transactions (incoming values in multiple currencies). 
And regarding when i need it - it depends. Both static reports in different currencies, but also ad hoc via e.g. Excel. 
Therefore, the key issue - by correctness of the calculation - is the performance.

Additional Case:
Of course we have a home currency (in this example, so U.S. dollars). Because of possible conversion errors, is it legitimate  to calculate the "Reporting"-currencys (USD -> AUD etc) from the home currency, although this is based on conversions? But I have not found the opportunity to do cross calculations with DAX.


----------



## Laurent C (Dec 18, 2012)

Hi Ugur, I have checked the model you sent me. According to what  you said above, you chose to have the TransactionAmount in your FactSale table recorded in the native currency. However, the data in the FactExchangeRate table do not follow this pattern. The DailyRate and AverageRate are equal to one if and only if [ToCurrencyKey]=1 (USD). Also the FactExchangeRate is not related to the ReportCurrency table. Having all transactions reported in the FactSale table in a single currency (say, in USD at the exchange rate that was valid at the transaction date) and then having a conversion rate for each foreign currency (for the transaction and for the payment date) would have several advantages, amongst which, having a simpler model.


----------



## Ugur Canbaz (Dec 18, 2012)

Hi Laurent,

thank you for your effort.

_--FactExchangeRate is not related to the ReportCurrency table._
FactExchangeRate is not related to the ReportCurrency, because when a Reportcurrency is selected we need to use a calculated measure to do  the conversion dynamically. I used therefore this Formula:
SumSales:=IF(HASONEVALUE( ReportCurrency[CurrencyName]); SWITCH( VALUES(ReportCurrency[CurrencyName]);"Euro"; [SumAmountEUR];"Rand";[SumAmountZAR];"Pound";[SumAmountGBP]; "US Dollar";[SumAmountUSD];BLANK());BLANK())

Also my "really" Currency-Table "DimCurrency" is definitly related to the FactExchangeRate. 

I think (and hope) it is exactly this, what my solution that i sent you doing is:  
_"Having all transactions reported in the FactSale table in a single  currency (say, in USD at the exchange rate that was valid at the  transaction date) and then having a conversion rate for each foreign  currency (for the transaction and for the payment date) would have  several advantages, amongst which, having a simpler model."_

_-- Having all transactions reported in the FactSale table in a single  currency (say, in USD at the exchange rate that was valid at the  transaction date)_
AmountUSD = [TransactionAmount]/CALCULATE(VALUES(factExchangeRate[AverageRate]))
_
--then having a conversion rate for each foreign  currency (for the transaction and for the payment date)_
As Pattern: 
CALCULATE(VALUES(factExchangeRate[AverageRate]); (FactExchangeRate[ToCurrencyKey] =XXX);
FILTER(ALL(FactSale); FactSale[TransactionDate] = EARLIER(FactSale[TransactionDate])))

For each foreign currency:
AmountGBP = [AmountUSD]* CALCULATE(VALUES(factExchangeRate[AverageRate]); (FactExchangeRate[ToCurrencyKey] =2);
FILTER(ALL(FactSale); FactSale[TransactionDate] = EARLIER(FactSale[TransactionDate])))

AmountZAR = [AmountUSD]* CALCULATE(VALUES(factExchangeRate[AverageRate]); (FactExchangeRate[ToCurrencyKey] =3);
FILTER(ALL(FactSale); FactSale[TransactionDate] = EARLIER(FactSale[TransactionDate])))

AmountEUR = [AmountUSD]* CALCULATE(VALUES(factExchangeRate[AverageRate]); (FactExchangeRate[ToCurrencyKey] =4);
FILTER(ALL(FactSale); FactSale[TransactionDate] = EARLIER(FactSale[TransactionDate])))

OK, my two questions are:
1. is the CALCULATE-Part of my solution basically correct?
2. Can i / should i use the three CALCULATE-Expressions for "each foreign currencies" in this form or better as a formula. And when better as Formula, then how?

Please excuse my questions. But I want to be sure before I present the solution.


----------



## marco.russo (Dec 18, 2012)

A completely dynamic measure based on you selection in ReportCurrency table can be the following one.
Please note that SUMX operates on a CROSSJOIN that has the purpose to minimize the number of inner CALCULATE executed, in order to maximize performance.

```
Amount :=
IF (
    HASONEVALUE (),
    CALCULATE (
        SUMX (
            CROSSJOIN (
                DimCurrency, 
                DimDate 
            ),
            CALCULATE (
               VALUES ( FactExchangeRate[AverageRate] ) 
                 * SUM ( FactSales[PaymentAmount] ) 
            )
        ),
        FactExchangeRate[ToCurrencyKey] = VALUES ( ReportCurrency[CurrencyKey] )
    ),
    BLANK ()
)
```

Marco


----------



## Laurent C (Dec 19, 2012)

Hi Ugur, it seems I misinterpreted your first comment.
Whereas a relationship between the FactExchangeRate table, and the ReportCurrency table is not required when using a "calculated relationship", it will greatly simplify the calculation within a single measure.
For the following, I added a relationship from FactExchangeRate to ReportCurrency and also set the relationship from FactExchangeRate to DimCurrency as *inactive*.


[Amount (reporting currency)]
:=IF (
    HASONEVALUE (ReportCurrency);
     SUMX( 
  VALUES(DimDate);
  CALCULATE( 
   VALUES( FactExchangeRate[AverageRate] )
                          * SUM(FactSale[TransactionAmount])   
  ) 
    );
    BLANK ()
)
</pre>
The formula has a simple interpretation: for each day, calculate the sum of TransactionAmount within the current context and multiply by the appropriate exchange rate, and return the sum over all the days.

Now, if you do not want to de-activate the relationship to DimCurrency, then an overwrite of the filter context is required. Here is one way to do it:


[Amount (reporting currency) - with active relationship]
:=IF (
    HASONEVALUE (ReportCurrency);         
     SUMX( 
  VALUES(DimDate);
  CALCULATE( 
   CALCULATE(
    VALUES( FactExchangeRate[AverageRate] );
    ALL(DimCurrency)
   )
                        * SUM(FactSale[TransactionAmount]) 
  ) 
           );
    BLANK ()
)
</pre>
Probably not the most efficient way to do it, but I wanted to minimize the difference with the previous formula.


----------



## Ugur Canbaz (Dec 18, 2012)

Hi at all.

can you please help me to find a one formula instead of the following calculated columns that i have?

AmountUSD = [TransactionAmount]/CALCULATE(VALUES(factExchangeRate[AverageRate]))

AmountGBP = [AmountUSD]* CALCULATE(VALUES(factExchangeRate[AverageRate]); (FactExchangeRate[ToCurrencyKey] =2);
FILTER(ALL(FactSale); FactSale[TransactionDate] = EARLIER(FactSale[TransactionDate])))

AmountZAR = [AmountUSD]* CALCULATE(VALUES(factExchangeRate[AverageRate]); (FactExchangeRate[ToCurrencyKey] =3);
FILTER(ALL(FactSale); FactSale[TransactionDate] = EARLIER(FactSale[TransactionDate])))

AmountEUR = [AmountUSD]* CALCULATE(VALUES(factExchangeRate[AverageRate]); (FactExchangeRate[ToCurrencyKey] =4);
FILTER(ALL(FactSale); FactSale[TransactionDate] = EARLIER(FactSale[TransactionDate])))

Thank you in advance!


----------



## Ugur Canbaz (Dec 19, 2012)

*[solved]  Powerpivot Currency Converter - multiple Report Currencys*

Hi Marco!
Hi Laurent!

Thank you very much indeed.
This Crossjoin thing is really very nice because very very performant. Together with the great book
Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model: Amazon.de: Alberto Ferrari, Marco Russo, Chris Webb: Englische Bücher
 for example like on pages 425 etc its a big fun to verify customer bi-requirements.
I hope soon a larger project with tabular model to be able to address.
But without you DAX evangelists it would be impossible to make this brave new technology better known.

Please stay as silk. 

My problem was finally solved with this:

Amount:=IF (
HASONEVALUE (ReportCurrency);
SUMX(
VALUES(DimDate);
CALCULATE(
VALUES( FactExchangeRate[AverageRate] )
* SUM(FactSale[AmountUSD])
)
);
BLANK ()
)

I'm sure in advance only calculates a column:
AmountUSD=[TransactionAmount]/CALCULATE(VALUES(factExchangeRate[AverageRate]); USERELATIONSHIP(FactExchangeRate[ToCurrencyKey]; DimCurrency[CurrencyKey]))

I wish you all the best.


----------



## Anwareljana (Feb 15, 2013)

> <!--?php
> //Get Posted data
> $amount = $_POST['amount'];
> $from = $_POST['from'];
> ...



Currency Converter


----------



## Gpal27 (Jan 9, 2017)

Hi Ugur,

I know it is very old post but I have the similar scenario where my Fact table has Native currencies (USD and CAD) but I need to display on excel with all 3 currencies in Native,USD and CAD.
If possible, I want to have a look at the relationship between Fact,Currency, Date and CurrencyRate table. (I am not able to see Image above from ImageShack)

Thank you very much.


----------

