Forcing Merged Calculations with the ForceMerge Function

By default, Web Intelligence does not account for merged dimensions in calculations if the merged dimensions do not explicitly appear in the calculation context.

Example: Calculating revenue with ForceMerge

This example has two data providers as follows:

Data Provider 1:

Data Provider 2:

If you merge the [City] dimensions, then create a table with [Country] and [Revenue], Web Intelligence displays the following result:

Because [City], the merged dimension, does not appear in the table, Web Intelligence does not take the merge into account when calculating revenue. Web Intelligence lists the total revenue in the second data provider against each country.

To display the correct result, replace [Revenue] in the second column with the formula ForceMerge([Revenue]):

Web Intelligence now takes the relationship between countries and cities into account when calculating revenue.

If [Revenue] is a smart measure in the above example, ForceMerge([Revenue]) returns #MULTIVALUE. This is because the grouping set (Country) does not exist for the [Revenue] measure. ForceMerge([smart_measure]) always returns #MULTIVALUE, unless by chance no aggregation is required to calculate the measure.