Let’s start with a simple example. Within a single calculation, all parts of a calculation must be either aggregated or not aggregated.
This calculation is not aggregated.
[Sales] / [Profit]
This calculation is aggregated.
Both of the calculations are valid. This calculation includes both aggregate, and non aggregated fields.
Sum([Sales]) / [Profit]
This calculation is not valid.
It gets more complex when using other calculated fields in a second calculation. In this example, I created a field called “Sum of Sales” with this calculation. Sum([Sales]).
If I use that field later, all other fields in the calculation must also be aggregated.
This calculation will be valid.
[Sum of Sales] / Sum([Profit])
Since [Sum of Sales] was already aggregated in a previous calculation, [profit] will also need an aggregation.
A few notes:
– Common aggregations include SUM, AVG, COUNT, MIN, MAX
– All Table calculations are aggregated
– Level of detail calculations using FIXED, INCLUDE, and EXCLUDE are not aggregated.