Power BI / Analysis Services – Aggregating Calculations

Many of Alistar’s solutions use the Microsoft BI Suite. In this process, the model is created in Analysis Services. This can be either a multidimensional model or a tabular model. A very powerful feature within these models is the ability to create calculations. These are calculated fields that can be defined within the models. The great thing about these calculations is that they make it very easy to calculate percentages, for example, which are then computed “on the fly” in the front-end tools.

A common question is whether calculations can be performed at a level other than the lowest level or the overall level. For example, a calculation that is performed at the project level and then added up to arrive at a total for the entire organization.

Different Types of Calculations

The image above clearly shows what happens to the results when a calculation is performed at different levels. All calculations are based on A*B. 

• The calculation in red is a simple calculation, which is therefore performed at every level. This results in a total of (26 × 25) 650
• The blue variant is calculated at the line level and yields a result of 87 (A*B at the line level, then summed)
• The calculation shown in green is performed at the project level and then added up to a total. This gives 32(8*4) + 154(11*14) + 49(7*7) = 235

The first two options are, of course, easy to implement by adding a calculation, performing the calculation in the query, or creating a calculated column at the dataset level, respectively. I will explain the third option below for the different types of models. This method is also well-suited for cross-fact calculations. This is because these calculations are based on metrics from different metric groups, which makes it impossible to add the calculation to the query. 

The table below clearly shows which method is best suited for each situation. A sample calculation has also been included, although this, of course, always depends heavily on the model's structure. 

	Within 1 measure group		About multiple measure groups
	Type	Example	Type	Example
Calculation at the lowest level	Query	revenue – VAT	Calculation (scope)	revenue – expenses
Calculation on a Different Level	Calculation (scope)	OHW at the project level	Calculation (scope)	Billable hours vs. revenue at the project level
Calculation at the highest level	Calculation	Profit Margin	Calculation	% Costs vs. Revenue

Multidimensional model

Within a multidimensional model, in order to perform a calculation at the project level, SSAS must be explicitly told at which level the calculation should be performed. This can be done using the SCOPE statement, which specifies the level at which the measure value should be populated. 

Procedure:

1. Create an empty “Named Column” or an empty field in the Data Source View
2. Add the field to the cube as a measure
3. In the "Calculations" tab, use "scope" to specify the level at which the calculation should be performed. In the example below, the measure [AB SCOPE] has been created and is calculated at the project level by adding the following code:
   SCOPE([Measures].[AB SCOPE]);
   SCOPE([PROJECT].[ID].[ID].MEMBERS);
   THIS = [Measures].[A] * [Measures].[B];
   END SCOPE;
   END SCOPE;

Tabular Model

In a tabular model, it is slightly easier to add the calculation. Within a tabular model, the SumX function can be used for this purpose. The calculation can be added at both the report level and the cube level. 

• Add a new metric to your report using the following formula
  SumAB_DAX = sumx(values(‘Fact'[PROJECT]), [SumA] * [SumB])
• Add a new measurement to your model using the following syntax
  sumx(values(‘Fact'[PROJECT]), [SumA]*[SumB])

Conclusion

The ability to add your own formulas and calculations to the various models is very powerful. However, it may sometimes require a fair amount of MDX or DAX knowledge. In this case, the calculations are easy to implement and can be of great value to the organization. However, be sure to carefully consider which calculation is best to use at any given time. In general: 

• Calculations at the lowest level in the query or calculated column
• Calculations that must be performed after the fact using a formula (for example, percentages)
• Cross-fact calculations that must be calculated retrospectively using a calculation
• Calculations that must be performed at a specific level, or cross-fact calculations that should not be calculated afterward using a calculation with "scope" or "sumx"