Introducing the filter context and CALCULATE

Understanding how totals are computed in Power BI

Let’s look in Figure 3-32 at the matrix we used for the last example.

FIGURE 3.32 The matrix shows the percentage of sales of the current brand over the total for each color.

It is interesting to question the meaning of each color row. It always shows 100%, so it is somewhat useless, and we might think about removing it from the report. However, as humans, we find that value reassuring. Seeing 100% gives us the nice feeling that the numbers in all the cells under it are correct. And, if we want to double-check this, we can just sum the Pct value for all the brands within a color to confirm that the total is indeed 100%.

Just look at the same report without the totals in Figure 3-33. Do you not feel an immediate need to check that the values in Pct account for 100% of Brown?

FIGURE 3.33 Removing the subtotals hides the 100% values and reduces our trust in the numbers.

When the total is computed by summing individual rows, its presence helps us trust the report. Nonetheless, this only applies to additive calculations. An additive calculation is indeed a calculation where the totals are computed by summing the rows. Most of the calculations are additive, and over time, we might get used to checking that a measure is correct by adding rows and checking the result. Despite all this, many interesting calculations, such as distinct counts and margin percentages, do not have the additive property.

For example, in Figure 3-34, you can easily see that Sales Amount is additive, whereas both Margin % and # Customers are not. # Customers is the distinct count of Sales[CustomerKey].

FIGURE 3.34 Out of three measures, two are non-additive. Only Sales Amount shows the sum of rows in the subtotals.

Non-additivity is a property of a formula. Forcing a non-additive measure to be additive is—usually—a mistake. Because DAX does not know whether a calculation is additive or not (to be honest, it knows to some extent, but this would be out of the scope of this section), it chooses to stay on the safe side. It computes subtotals by computing the same formula again and again in different filter contexts.

This behavior guarantees that the additive or non-additive totals are always computed correctly. Unfortunately, newbies may conclude that their measure is wrong just because the total is not the sum of individual rows. That would be an incorrect conclusion, at least for non-additive calculations. For additive measures, the total must be the sum of the rows. For non-additive measures, it is correct to show a total that is not the sum of individual rows.

There are techniques to force additivity for non-additive calculations, but we cannot introduce them now because they mainly rely on the concept of context transition—which we will see later. In this introductory chapter, it is useful to showcase a couple of scenarios where developers would expect additivity, whereas DAX is correct in performing a non-additive calculation.

One simple example is rounding. If we want a measure that computes the sales amount rounded to the nearest million, we can author a measure like the following:

Measure in the Sales table

Rounded Sales = MROUND ( [Sales Amount], 1000000 )

Once projected in a matrix, the result is non-additive because the rounding happens on the value of Sales Amount computed at the total level and not by summing the value rounded for each Continent, as shown in Figure 3-35.

FIGURE 3.35 Rounded Sales is non-additive because the total is rounded separately as opposed to it being the sum of the rounded values.

The sum of the rounded values would be 13,000,000. However, because Sales Amount evaluates to 12,337,640.39, its rounded value is 12,000,000, not 13,000,000. Despite being somewhat exaggerated, this example shows how the calculations happen in a matrix visual. DAX computes the total by executing the formula of the measure in a different filter context, where some (if not all) filters are missing.

This behavior is desirable and needed. Indeed, if Power BI were to compute the total by summing individual values, not only would it not work for non-additive calculations, but it would also create a situation where the result may differ depending on the number of rows in the matrix.

Let’s see another example where the formula’s behavior looks somewhat counterintuitive. Beware, the example would actually make little sense in a business context, but it does show you how DAX works.

We write a calculation that computes the sales amount and applies a discount of 5% if the sales amount is larger than one million. We can tell that it will not make sense to try to add together the values we find, as they are sometimes discounted and sometimes not.

The simplest way to demonstrate the behavior of this formula is to use two visual calculations. We will discuss visual calculations in detail later in the book. However, here is the code of the first, which just computes the discounted sales:

Visual calculation

Discounted Sales =
IF (
    [Sales Amount] >= 1000000,
    [Sales Amount] * 0.95,
    [Sales Amount]
)

The second visual calculation forces additivity over Discounted Sales:

Visual calculation

Additive Calc = EXPAND ( SUM ( [Discounted Sales] ), ROWS )

This formula sums the values of Discounted Sales for all the rows of the visual when the hierarchy of Country and Category attributes is expanded. At the total level, Additive Calc sums the rows of the visual for the column of the model you may be slicing by—in our example below, either categories or countries. While the value is the same as Discounted Sales for each row of the visual, the total is computed by summing individual rows in the total. In Figure 3-36, you can see two matrixes: one is sliced by Product[Category], and the other by Customer[Country]. The total of Discounted Sales is the same for both matrixes. However, the total of Additive Calc is different in the two matrixes because the individual rows have different values. Indeed, some will hit the discount threshold, and some will not.

When changing the contents of the visual, the very same formula ends up giving different results.

FIGURE 3.36 Forcing additivity provides different results, depending on the model column used to slice the values.

At the total level for Discounted Sales, we notice that the value obtained is 95% of the total for Sales Amount, simply because the total of Sales Amount is far above one million, and therefore, the formula applies the 5% discount to the total.

At the total level for Additive Calc, the value obtained is the actual sum of all the Additive Calc values for each row of the matrix (each Category or each Country). So, this total does indeed represent our overall revenue after the selective discount, depending on what we sliced by.

The Discounted Sales measure we authored is non-additive because it applies the discount if Sales Amount exceeds one million. The non-additive behavior can happen for several rows in the matrix; hence, the discount is applied to individual rows exceeding one million. However, individual values are not summed up in the total. The Discounted Sales measure is computed again, and because the total of Sales Amount largely exceeds one million, the discount is applied to the entire value of Sales Amount.

Despite not being intuitive, it is the correct behavior. Developers always have the option of creating additive calculations, as we did in this demo through a visual calculation, but they need to do so explicitly.