Switching Between Two Unit Sets, Power Pivot (ADVANCED)

[Blackhawks]

Hello Excel Gurus,

I'm working on a difficult problem that I believe will require a macros, but I'm not sure. I'm creating a dashboard using powerpivot and I have two sets of data that I would like to toggle between, Raw Data (15 columns) and Scores Based off of Raw Data (10 columns). I would like to add it so that I can just toggle between the two using a slicer, but other than adding all the columns to a hierarchy, which would look pretty sloppy, I'm not sure how to do this.

I found a really BAD way of doing this, which I know is going to cause me problems later, but I have doubled the data (I had 1000 rows of data and now I have 2000) and deleted the raw data from the first half and deleted the Scores from the second half and created another column that says whether the data is Raw data or Calculated data. This has allowed me to create a slicer to toggle between the two different data sets... I know this is a really bad way of doing this, but it's the only thing I have come up with.

Does anyone have any tips for me?

[mikeTRON]

Restructing the data like you have done might have doubled the datasize, but 2,000 records is nothing for excel, so why bother changing it?

I like the method as you now can use the native excel funcitions (like pivots) to get what you need.

[Blackhawks]

I would, but I know this will cause problems for me down the road.. It already has (a minor one), as when I create a pivot table, it won't let me take the "sum of" certain columns. Says there's a error calculating. I didn't bother trying to figure it out, because I know this method is not stable. I'm thinking about creating two separate tables and creating a dropdown macros with the code:

Please Login or Register  to view this content.

This will allow me to toggle between two separate tables/charts, but the problem with doing it this way, is that I plan on having 16 charts and tables in total. So I'm afraid this will cause a strain on excel... I just know there has to be a better way of doing this... Why is it so difficult to group column sets?