11 Replies Latest reply: Apr 26, 2016 3:00 PM by Jagveer Dhillon

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

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.

• ###### Re: how to hide a particular row in pivot but include it in the calculation

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

)

• ###### Re: how to hide a particular row in pivot but include it in the calculation

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.

• ###### Re: how to hide a particular row in pivot but include it in the calculation

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

Could you maybe post a small sample QVW?

• ###### Re: how to hide a particular row in pivot but include it in the calculation

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

• ###### Re: how to hide a particular row in pivot but include it in the calculation

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)

)

• ###### Re: how to hide a particular row in pivot but include it in the calculation

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

• ###### Re: how to hide a particular row in pivot but include it in the calculation

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'

))

• ###### Re: how to hide a particular row in pivot but include it in the calculation

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 %.

• ###### Re: how to hide a particular row in pivot but include it in the calculation

For the subtotal level calculation, try

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

edit:

Attached a sample QVW

• ###### Re: how to hide a particular row in pivot but include it in the calculation

All right, this might sound a bit irritating to you, but i tried this as well, and in this case, 2015-YTD gets suppressed since the month is null for that period.

• ###### Re: how to hide a particular row in pivot but include it in the calculation

You are awesome!!!!!!!!!!!