Riddle me this:  how do we take the first pivot below, and turn it into the second pivot?

PowerPivot Named Sets:  Original 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

PowerPivot Named Sets:  Desired Pivot

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:

PowerPivot Named Sets:  A Hidden Little Gem

Create Set Based on Row (or Column) Items:  a Hidden Little Gem on the Pivot Options Tab

Which gives us this dialog:

PowerPivot Named Sets:  A Very Simple 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:

PowerPivot Named Sets:  Different Measures Displayed for Different Years

Voila!  We See Different Measures Starting in 2003!

Note that this appears now as a single field in the field list:

PowerPivot Named Sets:  Everything Packaged Up as a Single Checkbox

Re-Useable!  Portable!

Just like one of PowerPivot’s other great benefits, portable formulas, Sets are portable too!

To illustrate, create a new pivot:

PowerPivot Named Sets:  Portable Across Pivots!

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 Don't Work With "Traditional" Pivots

Named Sets Do NOT Work on “Traditional” (Non-PowerPivot) Pivots – Yet Another Reason
to Use PowerPivot (or the 2013 Data Model)