To add a single total row in Pivot table for conditional dimensions.

[#1]

Hi All,

I am facing below problem with pivot table.

Situation: I have a pivot table with multiple dimensions which are conditional. i.e. a particular dimension in pivot table will be visible on selections in another list box. At a time a single dimension will be available always (Geography by default). See attached screenshot.

Requirement: I want to add a single total row at top. Irrespective of number of selections of dimensions; there should be just 1 row.

Challenge: If i enable the show partial for all dimensions then corresponding to all of them multiple total rows will appear if all dimensions are selected.

Any kind of help would be appreciated.

[prat1507]

Hi Shilpa

In Presentation tab, enable show partial sums for the first dimension, which I see is 'Region' in your case. For the rest of the dimensions, keep it disabled. Now select Subtotals in the top in the Subtotals option. I hope you'll get the desired result.

Regards
Pratyush

[#3]

Hi Pratyush,

Thanks for your response.

But the problem is that Region will not always be the first dimension. If i enable product and disable Geography, product will become first dimension.

And if i add show partial sums to product as well, then on selection of both there will be two rows of total.

Similar case applies to all other dimensions.

Regards

Shilpa

[stabben23]

Hi, have a look into this Application and see if this helps.

[#5]

@Staffan : I can't see any attachment !

[stabben23]

I'll send a better updated one:

[stabben23]

Do you use Ad hoc Dimension picked from a listbox?

[#8]

Not ad hoc.

What we actually do is ... we make selection in list boxes , some variable get set. The dimensions in pivot table are conditional on those selected variable.

[stabben23]

then look into my second atachment if you see it!

[#10]

Hi Staffan,

Thanks a lot ....the file was of great help...got a different perspective to solve this problem...working on it.

There is another perspective i am working on ......It would be great if you can provide your inputs

1. Added a blank dimension (unconditional) and enabled Partial Sums for it only. (See attached screenshot).

Problem is ..i have to hide this column...If i hide it using macro ...The "Total" will also get hide....Is there any way in QV by which we can shift position of this "total"  label?