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!
Best answer by Elliot
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]:
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]:
I saw your answer on the weighted average question.
If I understand it well, we need to do 1 metric with the totals and 1 metric with the details.
Isn’t it possible to have it in the same metrics because we need to have to total (not showing the details) and the breakdown when we expand the metric on a region or on channel for example.
Do you have a specific example I can help you with?
I think from your message about you should be able to leverage Show Value As or Calculated items. This method is described in the following article as “Option 2”
for example we have the revenue and the quantities for 3 regions so we do the calculation Revenue / Quantities = Unit price
We need to have the average in global which is not the sum of the average for the 3 regions but the calculation of total Revenue / total Quantities.
And we would like to have it in the same metric so we can first show the global view and click on the “+” to deep dive on the region level (and then again on an other spec per region)
