14 Replies Latest reply: May 25, 2012 1:25 AM by Jarno Loubser

# Conditional subtotalling in pivot tables

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.

• ###### Conditional subtotalling in pivot tables

You can use dimensionality() to show/hide/highlight different levels of the data. Works perfectly in Pivot tables.

dimensionality() = 0 means top level: Grand Total at the bottom of the chart in your case.

Regards,

• ###### Conditional subtotalling in pivot tables

Perhaps I misunderstand, but Dimensionality will let my decide which expressions are subtotalled, but not which rows within an expression is subtotalled?

• ###### Conditional subtotalling in pivot tables

I was suggesting to use dimensionality() in Text Color/Background Color of the Expression to hide numbers you do need to see.

• ###### Conditional subtotalling in pivot tables

I don't think you can suppress the undesired totals dynamically - it's "all or nothing".

Would make a nice "Idea" for R&D...

If it's "mission critical", I suppose you could generate an artificial "Total" Color in your data model as another dimension value, and assign the subtotal of the other colors of the value, but this looks like way too much work for the sake of an improved presentation...

cheers,

Oleg

• ###### Conditional subtotalling in pivot tables

Hi

I think you could hide the unwanted totals by using Dimensionality to select the correct totals level and an expression to count the rows, setting the total to zero when there is only one row. Oh, and suppress zeroes. I have done something similar recently.

Regards

Jonathan

• ###### Conditional subtotalling in pivot tables

Johnothan,

I played with using the noofrows() function and it does "find" the right records for me, but by setting the expression value to zero will I not effect the grant total? I'm only trying to suppress the subtotal (colour) row where n/a, but the grant total must include all values.

• ###### Conditional subtotalling in pivot tables

Jarno

Pivot tables do not sum the rows, rather the expression is evaluated at a detail, subtotal or total level. So setting the subtotals to zero should not affect the total if the expression selects the correct level to set to zero. Assuming the subtotal is at level 2, then something like:

=If(Dimensioanlity() <> 2, Sum(....), 0)

Tip: I usually temporarily add a column with expression =Dimensionality() just to determine the correct dimenionality value to use

Regards

Jonathan

• ###### Conditional subtotalling in pivot tables

Thank you - Getting close now :-)

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

sum(.....,0)

Is it possible to also suppress the 0 Subtotal rows?

• ###### Re: Conditional subtotalling in pivot tables

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

• ###### Re: Conditional subtotalling in pivot tables

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.

• ###### Conditional subtotalling in pivot tables

In include a mockup of what I need...

• ###### Conditional subtotalling in pivot tables

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,'') )

• ###### Re: Conditional subtotalling in pivot tables

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

• ###### Re: Conditional subtotalling in pivot tables

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?