skip to Main Content

Dynamic TopN PowerPivot Report, User Can Select a Measure and a Value for N

Check out THIS Bad Boy!

Old Tricks Combined With New

Folks, the combination of slicers and DAX is just…  amazing.  Do you SEE that picture above? 

If the consumer of the report wants to change it completely, and see Top 10 Customers by Total Paid (Sales), it’s two quick slicer clicks:

Dynamic TopN PowerPivot Report, User Can Select a Measure and a Value for N

How’d I Do It?

First off, it requires PowerPivot V2, primarily for the RANKX() function, which Colin has used so effectively in his Percentile Posts.

It also makes use of the V2 SWITCH() function, which Colin has also posted about, but that’s more of a convenience than a requirement – it just replaces some nested IF’s.

But fundamentally this is a variant of the “sort by slicers” trick that I posted about last year.  I recommend that you go re-read that one (or read it for the first time) because this trick very much “rhymes” with that one.

Download the workbook!

I don’t always do this, but since this is based on the Adventure Works sample data from Microsoft, there’s nothing sensitive about it.

Download the workbook here.

Isn’t that nice of me?  Well…

…Detailed Writeup Coming Thursday 🙂

I’m still feeling my way around PowerPivot V2, so when the idea for this technique hit me this morning, it took me longer than I expected to figure it out.

The technique isn’t difficult or time consuming.  But I hit a snag upgrading my Football workbook to V2, then I struggled a bit with the RANK.EQ function before switching to RANKX.  And RANKX is quite frankly harder to figure out than it should be – too flexible at the expense of basic simplicity.  Great for the Italians and Chris Webb, not so great for the Excel crowd (until you figure it out for the first time that is).

I also played around a bit with the new Measure Grid for the first time, and I have some notes from the experience.

Anyway, it’s already after noon on Tuesday and I like to get these posts up before it gets too late in the day.  So the detailed explanation of all of this will wait until Thursday.

Rob Collie

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 9 Comments
  1. Hi
    thanks for the great post.
    if i add another row labels (for example FrenchEducation), Is there a way to see the TOPN result ?
    I got this result:
    FullName FrenchEducation Total Paid
    Jorda Turner Bac + 4 $15,999
    Willie Xu Bac + 3 $6,839
    Willie Xu Bac + 4 $6,651
    Nichole Nara Bac + 4 $13,295

    Although I’d like to see the following result:
    FullName FrenchEducation Total Paid
    Jorda Turner Bac + 4 $15,999
    Nichole Nara Bac + 4 $13,295

    Thanks a lot

    1. Aha, you have discovered something I missed! Customer FullName is not unique! There is more than one customer named Willie Xu!

      When added together, they qualify for 2nd place by Margin. Separately, they’d rank 45th and 200+ !

      So I need to change the RANKX formula, I think, to use ALL(Customers[CustomerKey]) instead of ALL(Customers[FullName])

      Only problem is, when I do that, the pivot becomes *incredibly* slow. Unusably slow in fact, so I’m still digging a bit.

      1. OK I have a solution but it isn’t perfect. I’ll cover it on Thursday but will email you the updated workbook in the meantime.

  2. hey there.I’m trying to follow the adding a parameter havesting measure as described in your book on pages 90 / 91. I’m using Excel 2013.

    i created the data in excel, added it to the model and created the slicer. I added the max function in the measures.

    when i select different values in the slicer, the value within the pivot table always remains set to 2 (the highest value in the table).
    What am i doing wrong?

    1. Ok. i found the error of my ways: i need to leave the slicer as the last thing i add or it doesn’t work ..

      wheee. took me forever to figure it out!

  3. Hi Thanks for the post. I downloaded the workbook and followed the steps. It still shows the same rank as 1 for all of my top customers. I concatenated the customername with customerID to make it unique. Im not sure where im going wrong

  4. Hi, Great post and I got this working fine for a normal 1 to many relationship. Have you been able to make it work on a M2M relationship using a bridge table? I am mainly interest in dynamically sorting by different measures. I’ve been trying a number of different ways to restructure the formula but unfortunately I am not amazing at DAX! Is this possible?

Leave a Reply

Your email address will not be published. Required fields are marked *