Hi Pigment community,
I have a Metric ‘FX rates’ that contains the Conversion rates cut by three dimensions: ‘Currency’, ‘FX Type’ and ‘Month’.
Also I have a Transaction List that contain the above dimensions (as dimension property).
I’m looking to populate a column in the Transaction List that delivers the ‘FX rates’ value correspondent to the row value combination for the three dimension.
What would be the best formula structure to approach this task?
Thanks in advance :D
Hi Guillermo,
I think the best would be to do the conversion in a metric, by calling Transaction.Amount[BY: Transaction.Currency, Transaction.'FX Type', Transaction.Month] * 'FX Rates'.
If you wish to add the value anyway in a transaction property, it’s similar - you could call 'FX Rates'eBY: Transaction.Currency, Transaction.'FX Type', Transaction.Month] in your FX Rate property.
Note that although similar, the two BYs are structurally different. The first one operates an aggregation (e.g. SUM) and the second one an allocation (e.g. CONSTANT). Indeed, the transaction can be used as any other dimension - although the result of the formula can only either be by your transaction (e.g. transaction property) or not by your transaction (metric structure by dimensions).
If you wish to fiddle around those formulas, you create a dimension with the relevant properties and try in the formula playground. You can also create a copy of your transaction, create a unique ID if not in your current transaction and turn it into a dimension in the block settings.
Hope this helps!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.