Slicer Problem

[Sundance_Kid]

Hey

I am having an issue with a slicer on a file I have taken over from someone else.

I will try simplify the issue because it is a work related file and I don't want to reference work.

I have an input file with data. From the input file I am interested in the following columns:
1. "Version" - under Version there will be two options. January or June.
2. Country - Could have 10 different countries in the list
3. Cities within the Country - We could have 5 cities within each country - so around 100 cities in total
4. Totals $

So I have two pivot tables which I will be using to compare against each other in terms of $.

The first pivot table will have a Slicer which will select the January option in the Version column and the second pivot table will have a slicer which will select the June option in the Version column.
I will then have a Slicer for Country and I will have it so that the Slicer is connected and once I select a Country in the Slicer it will update both pivot tables simultanteously
I have another slicer for Cities within the Country. So again similar to the above, I will have the Slicer connected so it updates both pivot tables.

The issue I am having and I presume it is very simple but I cannot figure out is this.

When I select a particular Country in the Slicer - it updates the value for all the cities under that Country - which is fine and what I want it to do BUT the issue is that within both pivot tables it still shows the cities for all other countries, even though the amounts are blank in those cities as their Country was not selected in the Slicer.

I am expecting that when I click the Country only the cities from that Country will appear in the pivot not all cities across all countries?

Any idea what I am doing wrong?

Thanks

 

[Alex Blakenburg]

Are you by any chance using Power Pivot and the Data Model ?
And if that is the case do you want to show us a picture of your Diagram View.
Power Pivot > Manage > Diagram View
Do you have any tables are not connected or incorrectly connected ?

 

[Sundance_Kid]

Are you by any chance using Power Pivot and the Data Model ?
And if that is the case do you want to show us a picture of your Diagram View.
Power Pivot > Manage > Diagram View
Do you have any tables are not connected or incorrectly connected ?

Hi, thanks for your reply.

I am not using Power Pivot, just normal excel.

As I am not 100% familiar with the file, there are some other tabs with slicers. I wonder is it possible that there is some sort of connection between a slicer on one tab and one of the slicers in the tab I am having issues with.

Any other ideas? Otherwise I can look to cleanse the data and maybe attach the file here. Other option is to completely reset and create new tab with new view.

 

[Alex Blakenburg]

Is the the Pivot or the Slicer that is showing all cities instead of just the cities for that country ?

If it is the slicer can you
• Select the slicer
• Right click > slicer setting
• in the dialogue box check that "Hide items with no data" is ticked

 

[Sundance_Kid]

Hi,

So when I select the slicer "Country" and say select France - I would expect to only see the cities of France in the pivot. However, what I am seeing is all cities from all countries but only the values for the cities in France populated, the non French cities appear in the pivot but with blank values.

Whereas what I want to see when I select France in the Country slicer is all the cities for France only in the pivot with the values.

So I am wondering do I need to connect the Country Slicer with the City slicer or something.

I did what you said above about selecting Hide Items with no data but all cities still appeared even if the country selected was not related to them.

Thanks

 

[Alex Blakenburg]

This is going to be tricky without access to the spreadsheet.
If you get the name of the Pivot Table - PivotTable Analyze > PivotTable > PivotTable Name
And look at the connections of your slicers Version; Country; Cities do they all connect to that pivot table ?
Do they all have the hide items with data ticked ?

Do you have rows in the underlying data that have no value ?