Solved

Weighted average

  • 3 October 2022
  • 7 replies
  • 501 views
L
Rank
Userlevel 1
Badge +1
  • Loli Newly Minted Pigmenteer
  • 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

Elliot
Rank
Userlevel 5
Badge +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

A

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

E
Rank

Hi Elliot, 

 

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.

 

Thanks, 

Erik

Elliot
Rank
Userlevel 5
Badge +7

Hi @Erik 

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”

E
Rank

Thanks for your answer,

 

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

 

Elliot
Rank
Userlevel 5
Badge +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

G
Rank

Hi Elliot, 

Thanks for your explaination, it effectively works ! 

Gauthier and Erik 

Reply


Ask your question here!

Terms & ConditionsCookie settings