Semi-Additive Measures

Semi-additive measures are those whose values can be summarized for some dimensions of a cube, but not all. Ratios such as Average are also considered semi-additive measures.

For example, suppose you have a column that tracks employee headcount. The headcount yesterday was 10 employees, today it is 11, so does this make the total for the month 21? Definitely not! This is an example of a semi-additive measure. While headcounts of two regions or two departments can be summed up, the changes across time for a single region or department should not be summed up. So, headcount is a semi-additive measure across time. In this example, we want to look at the last non-empty value we have in a given month to determine our total headcount.

For this use case, you'd choose a Semi-Additive Measure option when you added the measure to your cube model, specifying the dimension over which the values should not be summarized (usually a date or time dimension). Instead, the query engine returns either the First Non-empty or Last Non-empty value in the result set when the results are sorted by time.

Note: In Tableau, semi-additive measures will appear as CNTD (Count Distinct) aggregations in the client application if the return value is an integer and AVG if the value is not an integer. However, the actual query will use whatever aggregate calculation you specified when the measure was defined in AtScale. This is because SQL clients do not understand the concept of semi-additive measures, and this is how AtScale informs the client that the measure is non-additive. Users should not change the aggregation function on the client-side in Tableau for these semi-additive measures.

Note: After upgrading to I2023.4.1 or later, you need to upgrade your current models to remove duplicate role-playing relationships. This ensures that duplicates do not appear when creating and editing semi-additive measures. For more information, see Changes Related to Upgrades.