Switch between Multiple Currencies using a variable. Is there a better way?

[#1]

Hi all,

I have 4 currencies, each with their own column in the fact table. The table is about 1.9M rows. At the moment I am changing between currencies based on a nested if in a variable that is controlled by the selected value in a currency drop down.

I find that this is quite slow, particularly when used in set analysis. Does anyone know of a better way?

Thanks,

James

[prieper · Accepted solution]

Have you tried to work with a variable - see the attached example.

HTH
Peter

[#3]

Thanks very much. This works excellently

[johnw]

The attached example may be more than what you're asking for, but it handles several regionalization details including the right currency amount and format. The desired currency is selected from a list box.

[#5]

Hi John, do you have the Currencies working with Ruppies and Yen?

Thanks

[johnw]

Scott Hughes wrote:Hi John, do you have the Currencies working with Ruppies and Yen?

No, but it's the same solution regardless of what currencies you use. The currencies don't matter - you just need to add them to the Currencies table and load conversion rates for them. The few currencies I included are examples of how to do that. And the base currency doesn't matter - you just have to select one.

Is there something strange about rupees or yen that makes it so they can't be handled this way? If so, you'll need to explain what makes them different so I can think about how to handle them.

In case it matters, here's the latest iteration of the file. Now it includes an intervalmatch to handle conversion rates being loaded at irregular intervals. But if all your conversion rates are loaded at regular intervals (daily, weekly, monthly, whatever), stick with the previous solution.

[AH]

Hi John/Peter,

I need to create a text box which calculates the Gross Revenue (Sum of Line Amount) for Transactional, Functional and Reporting Currencies.

but in one text box i would be able to switch between Currencies(Transactional, Functional and Reporting).

I have different Currencies(Transnational[ALL Currency], Functional[USD, GBP] and Reporting currency[USD].

Is there anyway you can help me with that? I would really appreciate your help.

Thanks,

Ahmed

[sunny_talwar]

Just a suggestion: It would be much more easier for you to seek better help if you start a new thread, because this thread is already being answered and not many people will come into a closed thread to check them out

Best,

Sunny