Riddle me this: how do we take the first pivot below, and turn it into the second pivot?
This is the Pivot We Have: 3 Measures (Total Sales, Sales to Parents, and Sales to Married Couples) Displayed for Each Year From 2001 to 2004
But This is the Pivot We Want: The “Married Couples” Measure Displayed Up Through 2002, But “Discontinuing” the Display of that Measure in 2003, Replacing it With the “Parents” Measure
I Can’t Believe I Haven’t Blogged This!
This is crazy. I was positive I’d blogged about Named Sets. But I searched, and it seems like I haven’t. Ever. And this is one of those cool little (and simple!) tricks that we all need from time to time.
OK. You cannot just filter out a measure in some places. It’s either on the pivot, or it’s not. Does that mean we’re stumped? Nope, we just need to use a different feature:
Create Set Based on Row (or Column) Items: a Hidden Little Gem on the Pivot Options Tab
Which gives us this dialog:
When the Dialog First Came Up, It Had More Rows in it. I’ve Already Deleted
Some Rows Like (2001, Parents) and (2003, Married)
Click OK, and we get the pivot we wanted:
Voila! We See Different Measures Starting in 2003!
Note that this appears now as a single field in the field list:
Re-Useable! Portable!
Just like one of PowerPivot’s other great benefits, portable formulas, Sets are portable too!
To illustrate, create a new pivot:
In a New Pivot, Merely Clicking on Set1 Gives You an Identical Rows Layout
Named Sets Don’t Work With “Traditional” Pivots
If you’re wondering why you’ve never seen this feature of Excel before today, there’s a simple answer: it only works with PowerPivot and Data Model pivots (as well as another flavor of pivot, the “OLAP” pivot, but that’s much less common).
Named Sets Do NOT Work on “Traditional” (Non-PowerPivot) Pivots – Yet Another Reason
to Use PowerPivot (or the 2013 Data Model)
