Supported Calculations
In your calculations, you can use different basic operators depending on the type of data in each field you use in the measure: numeric, single currency, multiple currency, or duration.
To see which measures and operators you can use in calculated measures, check the supported calculations for each data type.
Numeric values
In calculated measures, numeric values are percentages, integers, or floats. They don't return currency results.
This table shows which measures and basic operators you can use with calculated measures that return numeric values.
|
Data Type |
Plus (+) Minus (-) |
Multiplied by (*) |
Divided by (/) |
|---|---|---|---|
|
Single currency values |
|
|
— |
|
Multiple currency values |
— |
|
— |
|
Duration values |
— |
|
— |
|
Numeric values |
|
|
|
If the calculation you want isn't supported for multiple currency values but works for single currency, try consolidating or converting the field to a single currency field. For more information, see Currency Consolidation in Workbook.
Returned format for percentage values
When you combine percentage values with integers or floats in your calculation, the result shows up as a decimal value.
For example, if a field with the percentage value 200% is divided by the integer value 10, the operation calculates 2.0 divided by 10. Therefore, the result returns the decimal value 0.2.
0.2 = 2.0 / 10
The result that you obtain isn't a percentage value. The decimal value 0.2 is the same as 20%. If you'd rather see results as percentages in your pivot tables and charts, you can change the format to percentages. For more information see, Pivot Table Customization.
Single currency values
Single currency values are amounts that return values in one currency, like dollars.
This table shows which measures and basic operators you can use with calculated measures that return single currency values.
|
Data Type |
Plus (+) Minus (-) |
Multiplied by (*) |
Divided by (/) |
|---|---|---|---|
|
Numeric values |
|
|
|
|
Multiple currency values |
|
— |
— |
|
Duration values |
— |
— |
— |
|
Single currency values |
|
— |
|
Multiple currency values
Multiple currency values are amounts that return values in different currencies, like dollars and British pounds.
This table shows which measures and basic operators you can use with measures that return multiple currency values.
|
Data Type |
Plus (+) Minus (-) |
Multiplied by (*) |
Divided by (/) |
|---|---|---|---|
|
Numeric values |
— |
|
|
|
Single currency values |
|
— |
— |
|
Duration values |
— |
— |
— |
|
Multiple currency values |
|
— |
— |
If the calculation that you want to use is not supported for multiple currency values but is supported for single currency values, consider consolidating or converting the selected field to a single currency field. For more information, see Currency Consolidation in Workbook.
Returned values for multiple currency calculations
The results that you obtain depend on how the measure definition is built:
-
Calculations with numeric values - When multiple currency values are multiplied by numeric values, the calculation returns a result for each currency. For example, if a field with the numeric value 100 is multiplied by a field with the multiple currency values 10 USD and 20 EUR, the calculation returns a result for each currency: 1000 USD and 2000 EUR.
1000 USD = 100 * 10 USD2000 EUR = 100 * 20 EURNote:This also applies to multiple currency values divided by numeric values.
-
Calculations with single currency values - When you use multiple currency values and single currency values in a measure definition, the calculation only returns a result for the values that share the same currency. For example, a field with the multiple currency values 100 USD and 200 EUR plus a field with the single currency value 20 EUR returns 100 USD and 220 EUR.
100 USD = 100 USD220 EUR = 200 EUR + 20 EURNote:This also applies when you calculate multiple currency values minus single currency values.
Duration values
Duration values are those that return values in units of time such as hours, minutes, and seconds.
This table shows the type of measures and basic operators that you can use with calculated measures that return duration values.
|
Data Type |
Plus (+) Minus (-) |
Multiplied by (*) |
Divided by (/) |
|---|---|---|---|
|
Numeric values |
— |
|
|
|
Single currency values |
— |
— |
— |
|
Multiple currency values |
— |
— |
— |
|
Duration values |
|
— |
|