Solved

Weighted average

• 7 replies
• 502 views

Userlevel 1
+1
• Newly Minted Pigmenteer
• 4 replies

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

7 replies

Userlevel 5
+7

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

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

Erik

Userlevel 5
+7

Hi @Erik

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)

Same thing for the total of the months

Thanks

Erik

Userlevel 5
+7

Hi @Erik

If you follow the steps explained in Option 2 of this article you should get the desired result.

TLDR:

1. Create a Table with Revenue and Quantity metrics
2. Click on the Revenue metric and ‘Show Value As’ → ‘% of’ ‘Quantity’ Metric
3. Hide the Quantity Metric in the Pivot.
4. Change the formatting of the Revenue metric to be absolute (not percentage) so we show the Revenue per Unit
5. Create the desired view

I hope the above helps

Hi Elliot,

Thanks for your explaination, it effectively works !

Gauthier and Erik