ISFILTERED and removing of subtotals in visualizations

This article describes, how not to show subtotals in visualizations (typically in matrixes / pivot tables).

For example here we are going to keep blank subtotals for average price of Brand – but we still need to show the total price.

(If we didn´t need the total price, it would be easier to switch off subtotals in visual settings. But now some subtotals have to be visible and some have to be hidden.)

Average is calculated by this measure:

  • Average price without subtotals by Brand = IF(
       ISFILTERED(cars[Model]),
       AVERAGE(cars[Price]),
       BLANK())

Because:

  • ISFILTERED – defines, whether this row is filtered by something (now it is “model”). Aggregations are not filtered by model, so the result of “ISFILTERED” is FALSE. For specific model the result is TRUE.
  • AVERAGE – calculates the averages when filtered by model
  • BLANK – leaves it empty when not filtered by model

To see it clearly – if we used the ISFILTERED function itself, the result would be like this: