Conditional subtotalling in pivot tables

[jarno_loubser]

Hi,

I am trying to only show the subtotal row when more than one row is being totalled – without losing the grant total.

In the example below only Stock Fin Group = MFB should be subtotalled,not all the others because they don’t have different colour combinations.

[jonathandienst]

In my case, enabling "suppress zero-values" suppressed the subtotals of the level1 dimensions that had no level2 values.

[jonathandienst]

I am assuming that you have no other columns. If you do, then you need to ensure that they are also zero. Remember that suppress zeroes only works if all the expressions in the row are zero or null.

[jarno_loubser]

ok here is the best I could get. Not able to suppress the blank (0) rows, but good enough:

Here is the expression:

if(Dimensionality() = 0 or Dimensionality() = 1 or Dimensionality() = 3 or NoOfRows() > 1,

sum(Amount,'') )

[jonathandienst]

Your expression returns an empty string for the totals. An empty string is not suppressed, so it will display like your totals here. Make sure your expressions return 0 or null to be suppressed, and not an empty string.

Regards

Jonathan

[jarno_loubser]

Hi,

I know that the empty string will not be suppressed, but the zero does not suppress and looks worse. Here it the model - perhaps you can see what is wrong?