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

 
image

Yeah, It Really Works!

With great Excel skill comes…  insensitivity to others’ lack of it Smile

Let’s say you are a monster Excel pro.  You’re a pivot master.  Nothing is beyond you – even the more complex features of Excel seem easy.  That’s obviously a huge strength, an asset.  And PowerPivot magnifies those powers – it gives you a much bigger stage, makes you more important, and extends the reach of your work to a lot more people than before.  Great stuff.

But your skill level can also blind you.  The people who consume your PowerPivot applications and reports are not NEARLY as Excel-savvy as you.  The things you take for granted are often hard for them, sometimes even scary to them.

That can be frustrating of course, but remember:  if they understood Excel as well as you’d like them to, there wouldn’t be so much need for your skills.

Failure to understand a report is not THEIR fault.  It’s YOURS.

When you share a report with someone and they can’t figure it out, your first response may very well be to groan or sigh (inwardly), and mutter to yourself about how some people can’t seem to tie their own shoes.  Then you put on a helpful face and go explain to them how to use the report.  You may even say something like “hey, it’s actually pretty easy once you understand.”

That’s a tempting trap.  I’m not above it, trust me.  But I know that’s the wrong first instinct, to explain to them the mechanics of how to do it, or to tell them it’s actually pretty easy.  The right first instinct, the one I am constantly reinforcing with myself, is to think “how can I make the report easier to understand?”

And as your work becomes more important, and makes its way further up the leadership hierarchy of your organization, it becomes even more critical to have the right first instinct.

Example:  “I Can’t Sort the Report!”

Let’s say you have published the following mission-critical pivot report on UFO Sightings in the United States:

PowerPivot Report on UFO Sightings

And one of the report consumers says to you “great, but how do I sort by Average Sighting Length instead?”

Well, you and I (the Excel pros) both know about that little dropdown don’t we?

image

This Dropdown Scares Most People.  Seriously, it Does.

But that dropdown is scary.  Seriously.  The only people who don’t find it scary are Excel nerds like us.

And we, the Excel nerds, also know that we can right click in the Avg Sighting Len column and choose a sort option.  Normal people don’t know that.  Furthermore, that doesn’t work on SharePoint.  And really, the report consumer is used to simply clicking on column headers to sort – in just about every single application they have ever used…  except for Excel.

So in cases where sorting is important, can we give them something a little friendlier?  Yes we can.

Step 1:  Create Two Dummy Tables for Slicers

image  image

First table just lists all the measures you’d like the user to be able to sort by.  Second table is just Ascending/Descending (although as an added boost to friendliness, I came back and changed those to Largest to Smallest/Smallest to Largest because Ascending/Descending often confuses even me!)

Now you can add them as slicers on the report, even though they don’t do anything yet:

image

Step 2:  Create Measures that Detect User Selections on Those Slicers

[SelectedSortMeasure]=

IF(COUNTROWS(VALUES(SortBy[Sort Table By]))=1,
   VALUES(SortBy[Sort Table By]),
   “Total Sightings per Year”
)

[Selected Sort Order]=

IF(COUNTROWS(VALUES(SortOrder[Sort Order]))=1,
   VALUES(SortOrder[Sort Order]),
   “Largest to Smallest”
)

Both of those merely return the caption of whatever is selected.  And if more than one thing is selected on a slicer, it returns a default value – Total Sightings per Year in the first measure.

Step 3:  Create a 1,-1 Measure Based on Sort Order

[SortOrderMultiplier]=

IF([SelectedSortOrder]=”Smallest to Largest”,-1,1)

If the SortOrder measure defined above returns “Smallest to Largest” then this measure returns –1.  Otherwise it returns 1.

Step 4:  Create a Branching Measure Based of the “Sort Table By” Slicer

[HiddenSortMeasure] =

IF([SelectedSortMeasure]=”Avg Sighting Len (Mins)”,
   [Avg Sighting Length in Mins],
   IF([SelectedSortMeasure]=”Sightings per 100K Residents”,
      [Sightings per 100K],
      [Sightings per Year]   )
)* [SortOrderMultiplier]

This measure returns an entirely different value based on whatever the user selects on Sort Table By.  Sometimes it “mimics” one measure, other times another.

And note that last line – it multiplies [SortOrderMultiplier], which is 1 or –1, by the whole thing.

Step 5 – Add The HiddenSortMeasure to the Pivot, Sort By It

image

Sort the pivot by that measure.  Notice how it is the negative version of the Total Sightings per Year measure?  That’s expected based on the slicer selections.

Step 6 – Hide that Column of the Spreadsheet

image

And the result:

image

This is actually really easy.  Took a lot longer to write this post than it did to add to the report.

THIS TOPIC CONTINUED:

      Adding “sort by state name” to this report

      Try this report out live in your browser!

Next step, of course, is to make this thing look better, but that’s another post.

  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 24 Comments

  1. Thanx for this.
    I am yet to get fully into PowerPivot, but really enjoy reading along.
    The “sort the table by…” drop down box is something I have used into a few basic Excel (ie no macro) reports I have created for the business.
    I can absolutely vouch for how much easier/ better that approach is understood by the rest of the business (ie. the non analytsts, non Excel pros), than asking people to use Pivot tables or similar to do their own ranking and sorting.
    K

    1. Thanks Keith, and welcome!

      Just out of curiosity, how’d you hear about PowerPivot, and what is keeping you from getting fully into it?

      1. I built a quasi BI tool completely in Excel and have always been looking for low cost BI solutions to build on/ improve/ replace hat I was doing – and so I can across PowerPivot some time ago (it was called Gemini at the time).
        I don’t have widespread use of Office 2010 around me (I’m 1 of 3 test cases for an office of 200+) which is the main reason.
        Plus I am doing ok just using base Excel – at least in terms of front end user experience, maybe not as much for back end data transformation and storage.

  2. Rob,

    I really like this post especially the way it was written.

    You absolutely nailed it. I agree with Keith that the approach is excellent and I found it so instructive no matter what your level of Excel. We need to stop and think and find out what the user is saying and where they are in THEIR learning curve not OURS.

    Its Brilliant

    1. Thanks Jeff! I was wondering whether the intro to the post was going to “land,” I had a hard time writing it actually.

    2. ANd here’s some more kudos from another Jeff. This post is brilliant. A timely reminder about “the curse of knowledge”.

      I’ve recently stumbled across powerpivot myself – even though I’d condsidermysef a blackbelt (although not ninja) in other things excel – and I tell ya, I’m enjoying this blog just as much as this new bit of shiny excel kit!

      1. Hey thanks Jeff! I enjoy learning from everyone who comments here, too. Like Excel, PowerPivot is a canvas – creativity and ingenuity both have a big role to play, and sharing our little tricks improves everyone’s capabilities. Thanks for dropping me a note 🙂

    1. Great question Janet. Hold please… (and sorry that you had to post your comment twice – if the system hasn’t “seen” you before it requires me to approve your comments, otherwise I get flooded with spam comments. You can now comment at will, with no delay).

  3. Rob, I was completely with you until you had to put the words, “Right Click” and “Select” on that image near the end. A-ha! You aren’t really writing for the Excel nerds. At that point, I had to groan and wonder why some people can’t right-click their own shoes…”

    😉

    This is a hot trick. I love it. Another unconventional use for slicers!

    1. Heh heh, I knew one of you would give me grief over that. But I know some folks reading here are more of the BI/SharePoint Pro demographic, and sometimes they appreciate having things spelled out 🙂

      Slicers plus DAX and Excel formulas are nothing short of amazing. I think of them now as a general-purpose selection control, and not just as the built-in filters they are sold as.

  4. Really enjoy reading your blog and especially agree that, in general, if someone does not understand you, it’s your fault, not theirs (specifically Pivottable BI reports)

    Anywho, can you share this data set so we can play along?

    Thanks

  5. Awesome, I just had my light-bulb moment!

    I am one week into my PowerPivot revolution. Google led me to this particular post, which led me to the rest. This post was completely foreign at first, but I was finally able to work these measures into my dashboard.

    Thanks for all the posts here; this is such an informative site.

  6. Okay so i really enjoyed following this guide! Very well written, although I am not very familiar with powerpivot. Yet, i was able to do it!

    However, i have an issue which i could not find a way to resolve. What if my pivot table has more than 3 columns to sort?

    The if statements in the HiddenSortMeasure wont work since Measures accept up to 3 if statements.. Any solutions to that??

  7. It’s really annoying that this should be programmed whereas other BI vendors just have the option to sort ascending of descending by the measure

  8. In addition: i was talking about Pivot Charts 🙂 The sort by column option is not available there. Users will want to see a column chart sorted by the measure for easy spotting of the ranking.

  9. Hi Rob, I am a big fan of your books and website! I learned about this technique in power pivot alchemy. The sort by selected order measure is different in the book than this post. I’ve tried both ways but for some reason the hidden measure is not sorting largest to smallest? I think the multiplier is doing its job bc the hidden sort measure turns negative or positive when I toggle the sort order slicer. Any ideas why the numbers aren’t actually changing to a descending/ascending order though?

  10. Hopefully someone smarter than me will see this. I am slowly becoming an Excel nerd. I don’t fully understand these instructions… Step one: am I making those tables as a part of my main pivot? Step 2: where do I go to do this?

    I really need to be able to add slicers such that when the slicer is selected, the values sort from largest to smallest. I have 2 columns of data that this needs to work for. So if they select one column header on my slicer, itll show them the data sorted and if they click the other column header on my slicer itll show them that data, sorted.

    Can anyone help walk me through this?

    Thanks!

Leave a Comment or Question