I'm trying to understand whether I can return a Dimension/List item when referencing a Line item rather than the Line Item result itself
I have 2 modules. 1st module is dimensionalised by FX Rates (columns) & Regions (List Formatted Line Item)
The 2nd module has a Line Item which is List Formatted with FX rates & dimensionalised by Regions.
I want to be able to return the FX Rate for the each region dimension. I thought FINDITEM(FX Rates,FX Mapping.'Region (Text)') but this doesn't work as I need to return the List item from the Line item, not the line item result itself
I am aware it is easier to build the FX rates module with Regions as a dimension and then FX Rates as the List Formatted line item for future references but this problem has me intrigued
Solved! Go to Solution.
This answer should not be implemented and you're right that it's easier to build the FX Rates module with the Region dimension.
However, you can achieve this by using the TEXTLIST formula (works exactly like SUM formula but for text)
In the first module, create a text formatted line item called FX with the formula as NAME(ITEM(FX List))
In the second module, create a text formatted line item called FX with the formula: Module 1.FX[TEXTLIST: Module 1.Region]
This should work!
For a simple currency conversion to the "base" currency, the dimensionality of most of the answers above are the wrong way around
1. You have a module by Currency code that contains the rates (against the base currency)
2. You have a module by region with a formatted line item, formatted as Currency code
3. In the target module you can bring the regional FX rate by using a LOOKUP
e.g, Currency Calc.Converted = Local / FX Rates.Rate[LOOKUP: Region Details.Currency Code]
I've assumed GBP is the base currency (hence the "1") in the rate
If you want to return the "cross rate" calculations showing the local currency in different currencies, then you can perform the calculation in the target module (with Currency Codes as a dimension), by using the formula above with a * or / against the currency code list itself
e.g. Currency Calc.Converted * FX Rates.Rate
By the way, be wary of using Textlist. It is a very heavy calculation and can cause performance issues when used on large lists with a lot of text to summarise
I hope this helps