Work with Currencies in a Model with Measures

In a model with measures, each financial measure can have data from a single currency or multiple currencies. You can apply currency conversion too, letting you aggregate data from multiple currencies, simulate shifts in exchange rates, and plan on or across different currencies.

When to Use Currency Conversion

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.

You can use two methods to convert currency data:
  • Conversion measures that recalculate values from a base measure. In this case, entering data on one measure changes the other.

  • Conversion steps in data actions that copy data from a base measure, convert it, and paste it to a target member. In this case, the data is stored separately.

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

Displaying Currencies Without Conversion

There are a few cases where you don’t need to use currency conversion for monetary values:
  • You only have one currency in your model and you don’t need to see the values in any other currency.

  • Your model has a separate measure for every different currency, and you don’t need to make comparisons between values for different currencies.

  • Your model has different currency values within one or more measures, but you don’t need to see aggregated values across multiple currencies.

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.

Workflow for Setting Up Currency Conversion

The workflow for adding currency conversions to your model includes a few steps:
  1. Set up your currency conversion rate table.

    This table includes rates for all of the conversions that you’ll need. You can add rates for specific times, categories, rate versions, and rate types. Each rate table can be used by multiple models. For more information, refer to the Learn About Currency Conversion Tables page.

  2. Enable currency conversion in the model preferences.

    You’ll also need to specify the rate table and date dimension for currency conversion in your model, and generate a currency variable if you want your conversion measures to have their target currencies set via a variable. To learn how, refer to the Enable Currency Conversion for a Model with Measures section on this page.

  3. Set up one or more currency dimensions to specify how your base measures store different currencies.

    To do this, enable the currency column for a dimension that separates the data into different currencies. For example, if one measure has sales or payroll data in local currencies for different regions, you may want to set up currencies for an organization dimension. If another measure has different currencies that aren't directly linked to geography, you can use a generic dimension too. Each leaf member of a currency dimension needs to have a currency set. For more information, refer to the Learn About Dimensions and Measures page.

    Note

    Keep in mind that currency conversion won't be applied within the base measures; you're just identifying the currencies that exist in each base measure's data. Afterward, you can create currency conversions based on these measures.

  4. Configure your financial accounts and base measures.

    For measures, set the Unit Type to Currency and specify a fixed currency or the dimension that sets its currency. For more information, refer to the Currency Settings for Base Measures section on this page.

    If your model uses an account dimension as the primary structure, make sure that your financial accounts are set to Currency in the Units & Currencies column too, and that they have the correct rate types. For more information, refer to Attributes of an Account Dimension.

  5. If needed, add currency conversion measures in your model.

    You can also add conversion measures in stories and analytic applications, but they're easier to reuse when you add them to a model. These measures let you convert a base measure to a single currency or to multiple currencies set by a dimension. They also give you some options to specify which conversion rates are applied to the data. You can set up multiple conversion measures and then use them in calculated measures as needed. For more information, refer to the Adding a Currency Conversion Measure section on this page. Conversion measures also support data entry in planning models.

  6. If needed, add currency conversion steps to data actions to allow planners to copy data across different currencies.

    In a planning model, conversion steps let you convert and copy data between base measures. You may want to use these steps instead of conversion measures, or alongside them. For more information, refer to the Convert Currencies in a Data Action page.

  7. If needed, specify rate versions for the model's public versions.

    For a planning model, you can assign rate versions that correspond to specific rates in the currency conversion table. This lets you simulate additional exchange rate scenarios without using the rates for an entire category. For example, refer to the Plan with Currency Conversion page.

When you finish these steps, your model is ready to display the base measures and conversion measures from the model, and you can add more currency conversions in charts and tables if needed. For more information, refer to the Adding a Currency Conversion Row or Column page.

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.

Enable Currency Conversion for a Model with Measures

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.
Currency conversion enables the following features and settings for your model:
  • Calculated conversion measures
  • The rate type property for accounts and measures
  • Conversion steps in data actions (for planning models)
  • The rate version column for the version dimension (for planning models)

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.

Enable Currency Conversion for a Model with Measures Using Currency Variables

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.

If you want your currency conversion to be driven by a currency variable, specify the Currency Variable Settings:
  • Description: Add a description to the currency variable so that it’s easy to identify. The default description is set to Currency Variable.
  • Default Value: The default target currency used for currency conversion. This list provides every currency reference in the currency rate table. The No Conversion option is set by default.

Currency Settings for Base Measures

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.

Setting Description
Unit Type Set this option to Currency for all monetary values.
Currency
Select one of the options:
  • Fixed: If the measure contains values in a single currency, select this option and type the three letter currency code, such as USD. For example, group currency values would be stored in a fixed currency measure.

  • From Dimension: Select this option if the measure has values from multiple currencies.

    You’ll need a dimension with a currency attribute that specifies the currency for each leaf member. For more information, refer to the Learn About Dimensions and Measures page.

    Choose this dimension from the list.

    Values won’t aggregate across different currencies for this measure.

Rate Type

This option corresponds to the rate type setting in the currency rate table. The measure will be converted using rates with a matching rate type (blank, Average, or Closing).

You can apply average rates to transactions that take place over the course of the period, and closing rates to balances at the end of the period. For more information, refer to the Learn About Currency Conversion Tables page.

Note
If you have an account dimension as the primary structure, the account member sets this value, so it’s not necessary for the measure. For more information, refer to the Set Structure Priority and Create Custom Solve Order 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.

Adding a Currency Conversion Measure

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
    Select one of the options:
    • 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 Learn About Currency Conversion Tables.

    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.
    Note
    • Specific versions and calculations will only use rates from that rate version.
    • Generic rates without a category are not applied to the Specific category.
    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.

Restrictions

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.