how to hide a particular row in pivot but include it in the calculation

[#1]

I have a Pivot table where I just want to show the previous month and current month data but for the yearly data I want to include everything in calculation. As shown in the image below, the highlighted row needs to be hidden but included in the total calculations.

[swuehl]

Maybe you can use Dimensionality() to check on which dimensional level the expression gets evaluated, then branch into different expressions:

=If(Dimensionality() = 1,

    YourExpressionForPeriodTotal,

    YourExpressionForDetailsLevel

)

[#3]

Thanks for the reply swuehl.

I tried the below

if(Dimensionality()=2 ,

if(((Month='Current Month')

or (Month='Prev Month')),

(Sum(AVAILABLE)/Sum (DEMAND)),0),Sum(AVAILABLE)/Sum (DEMAND))

but it causes the Month dimension to suppress.

[swuehl]

How have you created your month dimension and linked to your calendar months?

Could you maybe post a small sample QVW?

[#5]

my calculated dimension for month is

=if((Month([REQ DEL DATE])=month(today()-30) and Year([REQ DEL DATE])=year(today()-30)),

'Current Month',

if((Month([REQ DEL DATE])=month(today()-60) and Year([REQ DEL DATE])=year(today()-60)),

'Prev Month',' '))

[swuehl]

So 'Current Month' is not a field value, that's why the condition like

Month = 'Current Month' is never true.

I was more thinking of something like this (maybe using set analysis instead of the sum(if()) 😞

if(Dimensionality()=2 ,

Sum(if([REQ DEL DATE]>=Today()-60, AVAILABLE))/Sum (if([REQ DEL DATE]>=Today()-60,DEMAND)),

Sum(AVAILABLE)/Sum (DEMAND)

)

[#7]

I tried this earlier , but this still doesn't hide the blank row in 2016-YTD period, as highlighted in my initial post.

[swuehl]

Remove the second ELSE branch from your calculated dimension and enable 'Suppress When Value is NULL' for your calculated dimension on dimension tab:

=if((Month([REQ DEL DATE])=month(today()-30) and Year([REQ DEL DATE])=year(today()-30)),

'Current Month',

if((Month([REQ DEL DATE])=month(today()-60) and Year([REQ DEL DATE])=year(today()-60)),

'Prev Month'

))

[#9]

I tried that as well, if I use suppress when value is null then it excludes the other values except the current and previous month in the calculation of the total service %.

[swuehl · Accepted solution]

For the subtotal level calculation, try

Only(Aggr( Sum(AVAILABLE)/Sum (DEMAND), Period))

edit:

Attached a sample QVW