Controlling Subtotals in Pivot Table when dimensions are switched.

[retko1985]

Hello,

please refer to the attached QVW.

I have Country as second dimension, because of Rank Sort. Now when I click Vencouver, I see subtotal for vencouver, but I want to see still for whole country (Canada).

Is it possible?

Thank you.

[Anil_Babu_Samineni]

Like this?

[retko1985]

Not exactly..

From my data the Country total for Canada is 60.

Basically what I want is when I click some city from Canada, it will display all cities from Canada in the graph, this is done by P in set analysis. Now I want to display total for Country under the selected City. (and also later I want to hide other subtotals, but this is probably for other question I guess).

Thanks

[sunny_talwar]

This?

[retko1985]

Hi, this is great.

Just one little thing, do you know how can I hide the country column and still make it work? Also similar, when I deselect all cities, there should not be country totals at each row, but this should be achieved when hiding country.

Also I just notice that total for all cities when no cities are selected is incorrect:

Thank you.

[sunny_talwar]

I think it would be helpful to see an Excel file with how you want to see the result with and without selections

[sunny_talwar]

Check the attached and see if this works... I have used a one time macro to hide Country dimension....

[retko1985]

I got a little confused with last message, so I had to delete it, but I see you already saw it.

Basically What i need is a little different (But I still can use these techniques).

Without selection it would be a basic view like this:

With Selection it would highlight the selected city, and below it the total for the county will appear, something like this:

Do you thing it is possible?

Thank you.

[retko1985]

I still see the 0 for cities which are not selected:

But look at my response above.

Thanks

[sunny_talwar · Accepted solution]

You keep changing your requirement... check now