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.