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]

Umm, this is a workaround I know it may not work that well or look good. But, you can add a calculated dimension.say 1, promote it to the top, disable partial sums in the presentation tab for all the dimensions except for this one. Select Subtotals on the top. And done!! PFA the .qvw. I'd used Staffan's file. Thank you and sorry Staffan!!

[stabben23]

No problems prat1507‌

[#4]

Thanks Pratyush.

Yeah..i did same.....But ...the problem is if i hide dummy column using Macro...is there a way to push the "total" label which appears on total line; to next column ?..Basically i don't want to hide the "Total" Label.

Any suggestions over this ?

[prat1507]

Hi Shilpa

Sorry to keep you waiting, I was away for a while. What you can do is hide the totals there on the chart as there's no way to move it to the next column, however you can add a text object as Totals keeping is position fixed and making the background transparent. This is not the best method but the end result is achieved. PFA.

Regards

Pratyush