# Listing/grouping variables in pivot table field list



## velinektori (May 22, 2014)

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 (Jun 20, 2014)

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 (Jun 26, 2014)

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


----------

