When creating charts that use currency fields you might wonder, as I did, how aggregration of records on a currency field works when there is a mix of records that use different currencies.
Does it use the base currency?
My first reaction was that CRM uses the base currency to display the chart and money fields are converted to the base currency. After all, every currency field in CRM has a second field named the same as the first with (base) added to the name. The base field stores the value of the first field converted to the base currency using the exchange rate that was defined at the time the record was created or the currency field was last modified. So it would be simple for the chart to total values in the base currency.
Sure enough, when creating a chart it didn’t matter whether I used the base or non-base version of a currency field, the chart always showed in the base currency.
Don’t jump to conclusions
But of course, that wasn’t really the case. I remembered the default currency option in Personal Options and I wondered whether this changes the way the chart is displayed. And it does. I was surprised and pleased to see that Charts with currency fields are displayed using the currency a user selects in Personal Options. If a user has not selected a default currency then the base currency is used.
Now, what exchange rate is used to display the values in the chart. Remember each record that has currency fields also has an exchange rate stored that is used to calculate the base value for currency fields. So the chart could use the exchange rate in the record. However, this won’t work when the user selects a currency in Personal Options that is not involved in a record.
For example, an opportunity record has US$5,000 as the Est.Value and the base currency is British Pounds. If the user selects Euros in Personal Options then charts must be displayed in Euros. However, the opportunity does not have the exchange rate for USD to Euros so CRM uses the current exchange rate for USD to GBP and GBP to Euro to work out the value.
Charts never lie…
If an opportunity in Euros is added to CRM, the record contains the exchange rate to the base currency, British Pounds. So when the user selects Euros you’d expect the Euros value to be taken from the record because it is already in Euros. But this does not happen, it seems that charts always show currency fields using the base value multiplied by the current exchange rate, even if the exchange rate is available in the record.
If the exchange rate is changed after a record is created, this has the confusing effect that a chart will show a different amount than on the record!
If you want the chart to always show using the base currency, then make sure you select the base equivalents for each currency field rather than the non-base field.