image

How can we call something SuperFriendly
if it’s not sortable by State?

Sorting by State!  How did I miss that?

In a comment on Thursday’s post, Janet asked an excellent question:  what about sorting by state name?

Awesome awesome awesome.  Thanks Janet – of course the people who use this report are going to want that.

Attempt #1 – Text Measures

Given that my trick relies on sorting by HiddenSortMeasure, I somehow have to get the state name reflected in that measure.  So I need a measure that represents StateName.

I don’t think this is widely known, but measures CAN return text.  Let’s write a measure that simply returns the name of the state:

[StateNameMeasure]=

IF(COUNTROWS(VALUES(States[FullStateName]))=1,
   VALUES(States[FullStateName]),
  
BLANK()
)

If you are wondering how that formula works, by the way, I highly recommend checking out the post where I explained IF(VALUES).

If I add that measure to the pivot and sort by it, I get:

image

Well, the measure worked.  But the sort order is awful.  Why is Missouri ahead of Alabama?  A programmer will surely scold me for asking that, but seriously, I have no clue.

OK, let’s try another technique.

Attempt #2 – Add a column to the States Table

image

Getting that column into PowerPivot was actually a bit trickier than I’d like it to have been, since this was a copy/pasted table.  If this were a serious production application, I would have been using SQL as the source for this table, and I would have asked my database colleagues to add it for me.

But since this is, ahem, UFO data, I pasted a second, two-column States table into PowerPivot – StateName and AlphaOrder columns and related it to my original States table (and be sure to treat the new table as the Lookup table!)  Then I used =RELATED() to add it to my original States table.

OK, now time for an AlphaSort measure:

[AlphaSort]=

MAX(States[AlphaOrder])

And that DOES sort properly:

image

BTW, I could have used MIN(), or SUM(), or even AVERAGE() instead of MAX().  I just needed something that returns the number.

Now I just need to add it to my slicer table, and add another clause to the IF() in my original [HiddenSortMeasure], and…

image

It’s Alive!!!!

A bit trickier than sorting by the other columns, yes, but doable.

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

    1. Thanks David. Yep, getting a lot of requests for it. Will share it out next week. Got a few more mods to make first 🙂

  1. I needed a dynamically generated (i.e. Calc Column) version of the AlphaOrder column that you made in Excel then pasted and related. I couldn’t find any thing about this on your site… or did I miss it? After some trial and error, I came up with this formula =RANKX(ALL(States), LASTNONBLANK(States[FullStateName],1), , 0)

Leave a Comment or Question