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!

 

[Ugur Canbaz]

[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]

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

//make string to be put in API
$string = "1".$from."=?".$to;

Currency Converter

 

[Gpal27]

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.