Solved

Weighted average

  • 3 October 2022
  • 2 replies
  • 86 views
L
Rank

Hi!

If I have this info in several rows, as a list of transactions:

  • One column with the amount in a local currency
  • One column with the amount in EUR
  • One column which tells me the month of the transaction

How can I calculate the FX rates per month, as a weighted average (not the simple average, but the average taking into account the weight of each transaction in the total amount of that month)?

Sorry maybe is so obvious how to do it, but I don’t know, I know how to do it exporting the transaction list and calculating it in the file but...not sure how to calculate it directly in Pigment, which is what I would like to do better. Thank you!

icon

Best answer by Elliot 4 October 2022, 15:50

View original

2 replies

Elliot
Rank
Userlevel 5
Badge +3

Hi @Loli

What’s the maths / logic behind the calculation you want to do?

Do you just want to do the Total Monthly Amount EUR / Total Monthly Amount Local  ? (calculating the Local > Euro FX?)

What does “Local” Refer to? is it local to the Region? Country? Entity? This is important information we need to consider in our calculation as well.

I think we should be able to aggregate the transaction data into a metric that is by Month and another dimension that contains the details of the local currency (e.g. by Country / Region)

Metric - Dimensions = [Month, and Local Dimension]: 

Transaction List.Amount EUR [by sum: Transaction List. Month, Transaction List. Local Dimension][

/

Transaction List.Amount Local [by sum: Transaction List. Month, Transaction List. Local Dimension]

Let me know how you get on

Tess.Hunt
Rank
Userlevel 4
Badge +1

Hi @Loli how did you get on with the information Elliot provided? Is there anything else you need from us on this?

Reply


Ask your question here!

Terms & ConditionsCookie settings