skip to Main Content

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)

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 20 Comments
  1. I have to admit, I have never ever clicked that button to see what it does. I’m sure that I have in the past jumped through all kinds of hoops to try to accomplish what this does. Thanks for highlighting the use of this feature!

  2. Oh my god… Yup this would have saved me a ton of time in formatting. This is not only useful for selecting particular measures for use for different fields, but for setting up custom mixed levels of aggregation (i.e. multiple items in the rows labels, opened up to various different depths). I have been tediously replicating such customs sets across multiple tables, and then doing things like suppressing the display of the “+” buttons for formatting reasons, when I could have just set it up once in this manner!

    I should heed my own advice, I always tell people: if you’re spending a lot of time doing something manually, take some time to see if there isn’t a faster easier way to get the job done.

  3. One small issue with this feature – you can’t (easily) have multiple customized versions of a measure in your report. For example if you have brought Measure A into your table twice (using the regular pivot table field list trick) and are displaying one with “no calculation” and one with some automated calculation like “percent of total”, creating the set will link the two measure columns; changing one to display as percent of total will change the other.

    Of course Rob would say: “don’t use the automated calculations, create an explicit measure!” It is probably also possible to set this up by using the MDX editor, but that is a whole other little rabbit hole that I want to leave aside for now.

  4. This is tremendous. I do a lot of process analysis in Excel, and this will make my life much easier when building metric distributions to determine metric goals.

  5. This is great! I had been hiding rows in order to bypass information, this will clean up several reports nicely.

    1. Be careful with that: if you are simply hiding the row, then if you have totals they would still include that row. Using this method you are effectively filtering out rows, and so the totals will not include the values for the rows you exclude.

  6. With a little bit of mdx knowledge you can even do a lot more. E.g. just coverting your set to mdx allows you to copy and paste it into different powerpivot models, if you are lazy ahh, I mean striving for efficiency like me 😉

  7. I’d love to use named sets more, but I’ve run into a fundamental challenge with using them…

    1) I’ve got a different collections of measures that I’m using over and over in multiple tables — sometimes as columns, sometimes as rows. At this point, I’m still refining my analysis, so when I work out a new measure, I’d love to just be able to add it into one master set, and have it automatically show up in all of my tables.

    2) When I use a set for the columns of a table, though, I apparently can’t use any of the measures within it as a value filter! I get an error message that tells me I need to remove that measure from my set, in order to filter on it.

    I understand that there may be some underlying aspect of how sets work that means what I’m trying to do doesn’t make sense, but it still rules out being able to take advantage of them.

    If there are more advanced ways to still filter a set-based table, based on values, then I’d love to learn more about them, but I’m also hoping to eventually turn these tables over to end users who (a) know how to use the built-in filters to dig in to pivots, but (b) wouldn’t be able to do any of the more sophisticated stuff on their own.

    Thanks in advance for any help.

    1. Oh, and I’ve started to dig into the MDX discussion you had here:

      since one of the main things my end users would want to be able to do is filter by top 10, etc. I think I’m starting to understand how _I_ could make that approach work, but it’s clearly not something I could ask them to do if they decided “You know what? I’d like to see the top 10 performers for each category of this table.”

  8. I’m trying to use a named set to create a dynamic date range of last 12 months. Creating the set definition using MDX is proving to be difficult. It seems to have a syntax of it’s own.

    1. Yeah, the difficulty of MDX is in some ways the reason Power Pivot exists. I never learned MDX, it was too hard for me 🙂

  9. OMG, named sets are fantastic! Finally converted an overweight Excel spreadsheet into Power Pivot. Named sets just saved me countless hours of drudgery!

  10. rob,
    I would like to use a set based on columns to get difference between current year and prior year. What should be mdx formula

  11. I really like the use of sets, and it has simplified the process of completing a “Budget vs. Actuals” / Assymetric Report. Has anyone been able to apply filters to a Single Column while using Sets????

    I’m fairly new to PowerPivot, and I’m having trouble getting my “Forecast Amount” Column to only show future months… Has anyone been able to do this successfully?

    If it’s helpful, the specifics on this scenario are: I am creating a PowerPivot Table that has 4 Columns from different tables….

    1. Funding[FundingAmount]
    2. Actuals[ActualAmount]
    3. Forecast[ForecastAmount]
    4. Calculated Column [RemainingFunds:=Funding[FundingAmount]-Actuals[ActualsAmount]-Forecast[ForecastAmount].

    I would like to have a Filter so Forecast[ForecastAmount] only shows future months (e.g., >=201605)… otherwise the “RemainingFunds” is incorrect because it counts both Actuals and Forecast for April (for example). I don’t think sets alone can accomplish this, what kind of formula would be required if done through a formula?


      1. Hi Jen,

        Yes, I found a couple of options that I’m using now.

        1. I created a Calculated Column called “Remaining Forecast” which looks like this in DAX: = IF(Fcst[FcstDate]>=201608, Fcst[FcstAmount]). This column shows only the forecasts for the month of August 2016 and going forward.

        2. One thing I didn’t like about Option #1 is that it only showed a lump sum “Forecast”. It’s more informative if I can see each remaining month. To address this, I created a Calculated Column for each Forecast Month remaining. It was a little tedious, but it produced the results I wanted. The only formula change is that I’d have it “=201608” instead of “>=201608”. Now the columns I’m able to display are:

        1. Funding[FundingAmount]
        2. Actuals[ActualsAmount] (through July 31, 2016)
        3. Forecast[ForecastAmount] for August
        4. Forecast[ForecastAmount] for September (and so on through the project’s period of performance)
        5. Calculated Column “Remaining Funds” = Funding – Actuals – Forecasts

        I hope this helps. There’s probably a much simpler way to do this, but with my knowledge base this works for now.

Leave a Comment or Question