How to suppress Rows in Pivot table but still have values show in subtotals?

[#1]

Hi All -

I'm working on a project where I need to essentially suppress certain ROWS from a pivot table where the dimension is equal to specific values.  However, I need the expression values on these rows to still show in my subtotals.

I'm able to create a calculated dimension to suppress the specific rows:

=Aggr(Only({$<[Lead Measure Name] -={'Actuals', 'Commitment'}>} [Lead Measure Name]), [Lead Measure Name])

However, I can't seem to figure out how to have the expression sums show in the total.  I've tried:

SUM(AGGR(Metric, [Lead Measure Name]))

However this does not work and I cannot use TOTAL or ALL since that then shows the total value on every single row.

An example would be (without the suppression):

Actuals = 3

Commitment = 2

X = 5

Y = 8

Total = 18

Desired Outcome With Suppression:

X = 5

Y = 8

Total = 18

Thanks for any help people can provide!!

[trdandamudi]

See if Dimensionality() or SecondDimensionality() will help. If not can you please post a small sample..

[#3]

I'm not sure how the Dimensionality or SecondDimensionsality functions work.  I was having a hard time following the examples provided via the help in QlikView.  But maybe I can use them?

Attached is a sample dataset:

The pivot on the left is the full dataset without any row suppression.

The pivot on the right is the pivot with the rows suppressed.  However, when you look at the TOTAL for either North or South you can see that the "Actuals" and "Commitments" are not showing up regardless of what I try in the expression.

Thanks!

[#4]

What I'm hoping to see in the Second Pivot is the two rows, but the totals rows show:

North = 22

South = 19

Grand Total = 41

[sunny_talwar · Accepted solution]

Try this:

If(Dimensionality() = 2, Sum({$<[Dimension 2] -={'Actuals', 'Commitment'}>}[Metric 1]), Sum([Metric 1]))

[#6]

Thank you!  This is exactly what I needed.