Work with Currencies in a Model with Measures

You can use currency conversion if you want to compare data across different currencies, or view or plan on aggregated values that include more than one currency.

For example, if you have a separate measure for each currency but you want to compare them, you’ll need to convert them into the same currency.

Or if your model includes sales revenue for several countries in different currencies, currency conversion lets you see the regional and global values.

Currency conversion lets you view your base measure values (such as local currencies or transaction currencies) as well as converting them to other currencies. This lets you aggregate data from multiple source currencies, as well as enter data on it for a planning model. You can also simulate the effects of different exchange rate shifts.

For currency configuration in a classic account model, refer to the Work with Currencies in a Classic Account Model page.

For measures with fixed currencies, you just need to specify the currency so that your data shows the correct units. Refer to the Currency Settings for Base Measures section for more information.

For different currency values within a measure, you’ll also need to add a currency column to one of your dimensions. Refer to the Learn About Dimensions and Measures page for more information.

For planning models, users can enter data on any measure that’s input enabled, and the other currencies will update immediately. Refer to Plan with Currency Conversion.

When your rate table is set up, you can switch on the currency conversion toggle in the Model Preferences ().

Next, specify the Currency Conversion Settings:

• Currency Rates Table: This table contains exchange rates that will be used to convert currencies in your model. Multiple models can use the same table, but it needs to have exchange rates for the different currencies, dates, rate types, and rate versions required for your currency conversions. Refer to the Learn About Currency Conversion Tables page for more information.
• Date Dimension: You can set up conversion rates for different dates to reflect changes in currency values, and this date dimension will determine which conversion rate applies to each fact.

Next, you can add the currency property to one or more dimensions. Refer to the Learn About Dimensions and Measures page. When that's done, check the currency settings of your base measures.

After you’ve set up currency, you can create a variable that drives currency conversion for measures that are bound to that variable.

With a currency variable, you can manually select any currency that’s part of a conversion table as target of a currency conversion. When you select a target currency, any conversion measure that’s bound to the variable gets automatically updated with the target currency set with the variable. It’s also possible to build calculations on top of conversion measures using that variable.

This provides more flexibility for currency conversion workflows. That’s especially useful if you need to update multiple measures in one go. While the variable default value is set in the Modeler, you can still overwrite the value in stories later on, either for the whole story, or for individual charts and tables.

If you want to delete that variable, you can only do so by disabling currency conversion.

Whether you enable currency conversion or not, you can store different currencies in your model using the unit type and currency settings when setting up a base measure.

For details about adding base measures, refer to the Adding Measures in the New Model Type page.

When your base measure is set up, you can use it as the source for currency conversion measures and conversion steps in data actions.

After your model has currency conversion set up and at least one base measure with currency, you can add conversion measures on top of it. These measures convert the source measure’s data into target currencies.

It’s common to use conversion measures to convert local currencies into a fixed currency so that you can work with data that is aggregated across the currency dimension. However, both the source measure and the conversion measure can either have a fixed currency or currencies set by a dimension property. For example, you can convert currencies set by a transaction currency dimension to local currencies set by an organization dimension. Additionally, a conversion measure can be linked to a variable and derive its target currency from that currency variable.

You can plan on conversion measures, and also base calculations on them. However, they're not supported as source or target measures in data actions.

In the modeler, currency conversions are set up in the Calculations workspace. (You can also add them in stories and analytic applications.)

1. To get started, select Add Currency Conversion Measure from the Add () menu in the toolbar, or from the list of conversion measures.

2. In the Properties panel, set the following options:

   Setting	Description
   Name	Type a unique ID for the measure using letters and numbers only.
   Description	Type a description for the measure to help your users identify it.
   Source Measure	Select the base measure whose values you want to convert. (Calculated measures aren't available here.)
   Target Currency	Fixed: To convert to a single currency, select this option and type the three letter currency code, such as USD. From Dimension: Select this option to convert to multiple currencies set by a dimension. From the list, choose a dimension with a currency attribute that specifies the currency for each leaf member. For details about setting up the currency attribute, refer to the Learn About Dimensions and Measures page. Values won’t aggregate across different currencies for this measure. From Variable: Select this option to drive currency conversion using the target currency set via a variable.

   Tip

   As you pick these settings, the Preview pane shows the values and formatting of your conversion measure. Use it to make sure that the conversion measure has the expected results.

3. If needed, specify Advanced Settings for the conversion. If you leave the default settings, each measure value will be converted with the rates that correspond to its date and category or rate version. Use these options to control which conversion rates to use.

   For more information about currency conversion rate settings, refer to the Learn About Currency Conversion Tables page.

   Category Option	Description
   Dynamic (default)	Use rates that apply to each value's version. If the source version has a rate version, only those rates can be used. Otherwise, the conversion will use rates for the source version's category if they exist, or generic rates with no category setting.
   Fixed category (for example, Actual or Forecast)	Apply the rates from the category that you choose to all the measure values.
   Specific	Apply the rates from a specific rate version to all the measure values. Choose the rate version from the list.

   Date Option	Description
   Booking Date (default)	Convert each value with the rate that applies to the value's date.
   Booking Date + 1 or Booking Date - 1	Convert each value with a rate that is offset from the booking date by the specified period. For example, convert a value for March 5 with the rate for April 5.
   Fixed Date	Convert all values with rates from a specific date.

   If the model has multiple date dimensions, remember that booking dates come from the one that's specified in the currency conversion settings of your model preferences.

4. If necessary, set the Formatting options. If your model uses an account dimension as the primary structure, you can ignore these options since the formatting from accounts will be applied instead.

   Setting	Description
   Scale	Simplify the display of large numbers by adding a scale. The scale shows up as a whole word for currency values in tables and charts.
   Decimal Places	Set the number of decimals to display.

This measure will convert source values from the base measure to the specified target currencies using values from the rate table.

Now that it's ready, you can create more conversions, add calculated measures based on the conversion, or work with the converted values in your stories and analytic applications.

The following usage restrictions apply when currency conversion is turned on in the model preferences:

• Base measures can't use NONE as their default aggregation type.