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 = nMonth, and Local Dimension]: 

Transaction List.Amount EUR mby sum: Transaction List. Month, Transaction List. Local Dimension]D

/

Transaction List.Amount Local uby 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?

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

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”

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

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