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:

Průměr tedy spočteme touto mírou:

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

Because:

  • ISFILTERED - určí, jestli se tento řádek filtruje podle něčeho (v tomto případě modelu). Souhrny se nefiltrují, proto v nich ISFILTERED vyjde jako FALSE, u konkrétních modelů pak vyjde jako TRUE.
  • AVERAGE - spočte průměr ve chvíli, kdy se podle modelu filtruje
  • BLANK - nechá prázdnou buňku když se nefiltruje (tedy se jedná o souhrn)

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

 

Leave a Reply

Your email address will not be published.

*

clear formPost comment