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:

enter image description here

  • I don't understand what you are trying to do. Pivot tables have grouping/collapsing functionality. Can you give an example? – Calvin May 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. – Jrintam May 22 '14 at 6:22
  • I edited a picture link to my original post, which will hopefully clarify what I want to do. – Jrintam May 22 '14 at 8:15
up vote 0 down vote accepted

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.

enter image description here

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.

enter image description here

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.

enter image description here

That's it!

enter image description here

  • 1
    Thanks a lot! My work contract ended, so I won't be able to test your solution. I will mark your post as the answer anyway, as it seems to be doing what i wanted =) – Jrintam Oct 22 '14 at 19:44
  • Shame I only saw your question recently... still, at least you know for next time :) – Andi Mohr Oct 23 '14 at 8:18
  • Actually I've found myself using this technique in my own PowerPivots lately... Thanks for asking the question! – Andi Mohr Oct 30 '14 at 19:16

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.