Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
Vladimir
Perhaps I misunderstand, but Dimensionality will let my decide which expressions are subtotalled, but not which rows within an expression is subtotalled?
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
I was suggesting to use dimensionality() in Text Color/Background Color of the Expression to hide numbers you do need to see.
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
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.
In include a mockup of what I need...
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
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?