Hi Holly,

It's a bit difficult to picture from your description. In particular this sentence isn't clear "I have a data set with both rows and columns that ideally I would both group". I can't imagine what you mean by grouping rows and columns...do you mean group both rows and columns together, or group rows, and separately group columns? A screenshot or sample file would be helpful, but let me try.

In the Slicer connections list you will see a list of PivotTables, not Pivot Charts. Each Pivot Chart has it's own PivotTable, so if they all share the same cache then you'd see them in the list of Slicer connections.

If you want to group items and have a Slicer for those groups then you need to add a column to your source data for this grouping. e.g. let's say you have a list of produce in a column called 'Produce'. This produce column contains the following items: apples, pears, oranges, potatoes, corn and carrots. You want to group these items into departments; fruit and vegetables and insert a slicer for the departments. So, you add another column to your source data for the 'Department' and the items in that department will be Fruit and Vegetables.

I hope that points you in the right direction. If you're still stuck, please upload a sample Excel file so we can give you a specific solution.

Mynda

Hi Mynda,

I'm halfway there, thank you!  Sorry I wasn't clear - I did mean to group rows, and separately group columns.  I've attached a sample of my file, but basically this is info for a group of nonprofit health centers, and we surveyed the people who go.  The questions in rows I was able to group as you suggested using an added column I could use a slicer for - questions around service, staff engagement, etc.  The columns I'd also like to group - they are things like Male, Female, Once a Week, Twice a Week, etc. - I'd like them to be categories like Gender, Frequency of Use, etc.  My solution was to make an entire pivot chart for just Gender, and a separate one for Frequency and so forth, changing the groups of questions for every chart at once using a single slicer, since the questions would be the same for each.  That's where I ran into the problem with the pivot cache.

Since the table is half sorted into groups, maybe I will copy it many times to get 1 cache, then filter a table for Gender, the next Frequency etc. and build charts from each of those.  I don't know if that is the best solution, but it will be my next plan of attack!

By the way, I first learned the joys of slicers from your webinar on building dashboards, so you have helped me twice!  I am trying to get my stingy company to get me the full course - I think I'm wearing them down.  But thank you!!

Holly

Hi Holly,

Thanks for sharing the data. It looks like the data is already partially summarised and pivoted and this is the cause of your problems. Nothing to do with caches.

If you can get the original data you can then use the PivotTable to summarise it. For example, I'd expect to see columns for:

• Category
• Characteristic
• Age Group
• Gender
• Frequency
• Seeker Type
• Membership type (? Family, Senior, Passive)
• Visit Time
• Score

This will allow you to insert Slicers for the Gender, Frequency etc.

Mynda

Thanks Mynda,

I have a message in to our survey company who originally gave us this information.  They use Alteryx rather than Excel, so there is no telling what the data will look like if I can view it, but I will figure it out if they can send it! 

The 'Activity Category' column on the second tab was something I added based on your suggestion, which has already helped a lot.  Thanks again!

Holly