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 DeletedSome 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 Reasonto Use PowerPivot (or the 2013 Data Model)**