Dynamic TopN Reports Using PowerPivot V2!

 
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.

8 Responses to Dynamic TopN Reports Using PowerPivot V2!

  1. DAN says:

    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
    Dan

    • powerpivotpro says:

      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.

  2. alex says:

    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?

    • alex says:

      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. Sayrah says:

    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

Leave a Comment or Question