Hi everyone, I have yet another doubt. I have a pivot table that consists of the gmv from each client from 3 countries. The gmv for each country is in its own currency. The data for all three contries is stored in the same table, with an additional column that displays information about the country. However, I also want to be able to display the gmv obtained in USD.
Since each country has a different exchange rate, how do I connect the filter for country with the exchange rate required, i.e.; if a user selects ‘country 1’ on the filter pinned to the top, how do I display the currency for that country only, in USD, using its own conversion rate? For example, country 1 has an exchange rate of 1USD = 4, Country 2 has an exchange rate of 1USD = 50, etc.
Initially, the data for each country was in separate databases, which made things easier as there was no drop down selection menu and the currency exchange rates were consistent too, but this is different.
Edit: I’ll be using the LAC-A formula that @David_Wong suggested in an earlier post i made here as certain gmv values get duplicated. The reason I’m using that is because gmv is calculated through the orders placed, each of which have their own unique ID. Each order ID can consist of multiple items, which are the orderItemID. Since I’m using the orders and orderitems table combined joined together, this causes the gmv values get duplicated, causing the values to blow up
Thank you!