Solved

Populating a Column in a Transaction list from a Metric

  • 6 January 2023
  • 1 reply
  • 137 views
Guillermo Puls
Rank
Userlevel 2
Badge +2

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

icon

Best answer by francois 6 January 2023, 14:01

View original

1 reply

francois
Rank
Userlevel 6
Badge +14

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'[BY: 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!

François (Pigment)

Reply


Ask your question here!

Terms & ConditionsCookie settings