In a pivot table, I have sales data by country for different years. As sales are in local currency, they are multiplied by the exchange rate for the relevant country for sales in Euros.
The problem is with the subtotal in Euros when there are several countries with different currencies. If one country is selected, or countries which use the same currency, there is no problem.
Here is the expression which as the problem in the subtotal:
if(Dimensionality()=1 and SecondaryDimensionality()=1,
sum(Sales)*[exchange rate],
if(Dimensionality()=0 and SecondaryDimensionality()=1,
sum(total <Period, Market> Sales)*[exchange rate]
)
)
I have tried using AGGR and TOTAL, but couldnt get the right combination.
It doesn't have to do with the currency being different but with the way you have your data model. Your expression:
sum(Sales)*[exchange rate]
sums all the Sales first, then multiplies by exchange rate. But your data model has an exchange rate for each record. So therefore you want to multiple the 2 first, then sum that. So I changed it to:
It doesn't have to do with the currency being different but with the way you have your data model. Your expression:
sum(Sales)*[exchange rate]
sums all the Sales first, then multiplies by exchange rate. But your data model has an exchange rate for each record. So therefore you want to multiple the 2 first, then sum that. So I changed it to:
