June 24, 2016
I have a data set with both rows and columns that ideally I would both group (they're not dates, unfortunately) and add a slicer to, but as I can't do that with slicers as far as I know, I'd like to only slicer the rows and have a different pivot chart for each column group. However, I can't seem to make one slicer control all the charts I need, even though the rows are identical. The separate charts are not appearing on the list of possible slicer connections - I suspect they have different pivot caches. I am using the same data set to make the charts - copying it and then modifying the copies - but apparently that is not enough. I think the only solution is to make charts from different pieces of 1 pivot table. Is that even possible?
I am certainly open to other solutions... I have been banging my head against this for days so a fresh perspective is welcome. Thank you!
July 16, 2010
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.
June 24, 2016
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!!
July 16, 2010
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:
- Age Group
- Seeker Type
- Membership type (? Family, Senior, Passive)
- Visit Time
This will allow you to insert Slicers for the Gender, Frequency etc.
June 24, 2016
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!