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:

I don't understand what you are trying to do. Pivot tables have grouping/collapsing functionality. Can you give an example?
– CalvinMay 21 '14 at 21:42

I have one pivot table with all the variables and I would like to group the variables into subfolders, so they're easier to find, without creating another table. For example I have table with few different throughput measurements and error rates. I would like to group the throughput measurements into their own "sub folder" and error rates into their own "folder", in a way that they're still individually selectable.
– JrintamMay 22 '14 at 6:22

I edited a picture link to my original post, which will hopefully clarify what I want to do.
– JrintamMay 22 '14 at 8:15

1 Answer
1

There is a way you can do this. For each "folder" you'll need to create a dummy PowerPivot table to hold the calculated measures.

The simplest way to do this is to create some linked tables. For example, let's say you want "folders" to hold your Errors and Goals measures. Create these tables on your worksheet and for each click the Create Linked Table button on the ribbon.

In the PowerPivot window make sure these new tables are named as you desire - these will be your "folder" names: I've used VarErrors and VarGoals. Don't try and link them to any other table - allow them to be independent.

Now you can start allocating measures to folders. For each existing measure, edit it (go into Measure Settings and set the Table name to be the variable "folder" that you want.