PowerPivotPro

PowerPivotPro is Coming to Atlanta

March 20 - 22, 2018

Registration for 2018 Public Training is now open!

AVAILABLE CLASSES

**Use the discount code “3ORMORE” when signing up 3 or more people.

MARCH 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Austin Senseman

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work! Two Days in our class and you are EMPOWERED!

  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • Taught by Austin Senseman – PowerPivotPro Partner
  • You don’t need to be an IT professional – most of our students come from an Excel background
Atlanta Public Training Classes
Atlanta Public Training Classes

MARCH 20 - 21

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

The Advanced DAX Course was such a hit in the first half of 2017 that we’ve expanded the course to 2 days!

Overview

  • This advanced DAX training class is taught completely in Power BI Desktop
  • Students are encouraged to take our Foundations course and have hands on experience with the DAX language
  • Taught by Ryan Sullivan – Principal Consultant
  • Class material drawn from usage of Advanced DAX applications while consulting with hundreds of international firms

MARCH 22

Level Up Series: Power Query for Excel & Power BI

Instructor: Ryan Bergstrom

The second class in the series is our Level Up Series is Power Query for Excel & Power BI.

  • Students are encouraged to take our Foundations course and have hands on experience with Power Query in Excel or Power BI Desktop
  • Taught by Ryan Bergstrom – Principal Consultant and Trainer
  • We will cover common to unique business challenges made easy with Power Query’s data wrangling capabilities
  • Intermediate to Advanced Level Power Query best practices distilled into easy to understand patterns to apply to your most common business challenges
Atlanta Public Training Classes
PowerPivotPro Logo

 
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.

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

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
    Dan

    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 Comment or Question