Slicer Problem

Sundance_Kid

New Member
Joined
Sep 2, 2017
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,068
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 2, 2017
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
1,068
Office Version
  1. 365
Platform
  1. Windows
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

1620698519166.png
 

Sundance_Kid

New Member
Joined
Sep 2, 2017
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
1,068
Office Version
  1. 365
Platform
  1. Windows
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 ?
 

Forum statistics

Threads
1,136,194
Messages
5,674,362
Members
419,504
Latest member
NeilG2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top