Powerpivot Currency Converter - multiple Report Currencys

[Ugur Canbaz]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.