Using relational calculations
You build calculations in the expression editor using functions.
If a calculation is used in multiple reports, or by different report authors, ask your modeler to create the calculation as a standalone object in the model and include it in the relevant package.
You can add calculations to lists, crosstabs, and all other data containers. You can also add calculations directly to a page header, body, or footer. However, you must first associate a query to the page. For more information, see Add a page to a report
Units of Measure
When creating calculations, you might encounter problems with the units of measure. For example, the calculation Cost*Cost returns the unit of measure * instead of a currency unit of measure. To avoid this problem, change the format of the corresponding column to obtain the desired unit of measure.
Calculation Solve Order
When calculations in the rows and columns of a report intersect, calculations are performed in the following order: addition or subtraction, multiplication or division, aggregation (rollup), and then the remaining arithmetic functions.
The remaining functions are as follows:
- absolute, round, round down, average, minimum, maximum, medium, count
- percentage, % difference (growth) or % of total
- rank, quartile, quantile, or percentile
If both calculations have the same precedence, for example, if they are both business functions, then the row calculation takes precedence.
Limitations of Calculations
You should use only the expressions and functions available in Reporting, and follow their syntax.
Minimal checking exists for calculations. If your calculation uses an invalid expression, your report results may contain unexpected values.
In addition, you should define member summaries as follows:
summary_function (currentMeasure
within set
set_reference)
where set_reference is a level or set inserted from the Source tab .
Unless otherwise required, summary_function should be the aggregate
function. If you use an explicit summary function, you may encounter problems with measures and
scenario or account dimension members (such as profit margin, distinct count, and so on) that have
complex rollup rules, or members that do not roll up.
Know your data, and confirm with the owner of the cube where overriding the automatic aggregation is safe.
Because of these limitations, summaries of calculations may not provide reliable values. For
convenience, you may have to build reports where row summaries and calculated member columns
intersect. In such reports, these intersections may contain unexpected values. In contrast, row
calculations intersecting with column aggregates using the aggregate
function are
safe because the calculation is performed on the reliably summarized values.