I have data with 60+ variables and I would like to group the variables into different collapsible fields in a pivot table, similar to having different tables in the pivot field list - ie, instead of having all 60 variables in the same long list. A bit like creating sets, but allowing the set members to still be chosen individually.
I have tried splitting the variables into different pivot tables, but it's a tedious process to do manually and hard if not impossible to do automatically using PowerPivot. I have also tried to split them with perspectives, but that is a bit difficult to maintain, because the variables change over time and I would like to make it as easy to use as possible (the end user wouldn't have to touch anything but the charts or maybe some macro button). Is there a simpler way of doing this?
I'm happy with either PowerPivot or a normal pivot table based solution. Using VBA isn't a problem either.
Edit: Here is a picture that hopefully demonstrates what I am trying to achieve, grouping without splitting data into multiple tables: