Listing/grouping variables in pivot table field list

[velinektori]

I have data with 60+ variables and I would like to group the variables into different collapsible fields in pivot table similar to having different tables in the pivot field list 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 power pivot. 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 normal pivot table based solution. Using VBA isn't a problem either.


Here is a link to picture that hopefully demonstrates what I am willing to achieve, grouping without splitting data into multiple tables http://tinypic.com/r/jrtq38/8[h=1][/h]

 

[tjdurkin]

Hi velinektori,

Rather than split into separate data tables, try the 3 quick steps below...

1. Create lookup tables for each attribute group (notable1, notable2, etc). (Lookup tables can be created very easily through the use of the "Remove Duplicates" button on the Data tab (copy columns dummy1, dummy2, and dummy3. paste to a blank sheet. click Remove Duplicates. Create a table and Link the table to the data model)

2. Create relationships from your main table to these lookup tables (the arrows should point to the lookup table in diagram view).

3. Hide the attributes (variables) in your main table from Client Tools (see Hiding Columns from Client Tools link below). The greyed out attributes below have been hidden from Client Tools.

You should end up with a FieldList similar to your snapshot and all metrics (aggregation columns that will go into the values section of your field list) will be in their own table.



Hiding Columns from Client Tools

Tip: You will likely need to concatenate your notable1 & notable2 attributes together in both your metric and your lookup tables in order get a distinct key to get the relationship to create.

 

[velinektori]

Hi tjdurkin!

Thank you for your comprehensive answer! This is exactly what i was looking for.

Edit: I asked the same question in Superuser, do you mind if I copy your answer there too in case someone else is wondering the same thing? Or alternatively you could do that yourself if you want points =P. Here is a link to the question http://superuser.com/questions/757023/listing-grouping-variables-in-pivot-table-field-list