Chapter 2: Slicers: The Gateway to Interactivity
The intro to this chapter is longer than the others and offers a glimpse into the “behind the scenes at Microsoft” history of slicers, in two parts. We promise it’s both relevant
and
interesting because slicers are
both
an incredibly useful feature
and
they provide a window into the “soul” of our lives as “data” people.
Part I:
In 2006, I (Rob) left the Excel team to join another team at Microsoft that was working on fantasy football. (Fantasy football is a mathematical game cloaked in the trappings of American football. The siren’s song of being paid to pursue my already-obsessive hobby was just too strong to resist.)
It wasn’t long before I convinced my new team to spend large amounts of money on something we dubbed “Statosphere”—Excel PivotTables in the cloud (hosted on the web version of Excel), powered by an expensive OLAP database. (You don’t need to know details about OLAP—just that it is a forerunner of Power Pivot.) The expensive OLAP database, in turn, was powered by even
more
expensive raw data from STATS LLC. The total price tag of this boondoggle approached half a million dollars, once Microsoft employee salaries were figured in, and it probably cost that team’s general manager her job. (But let’s not focus on the negatives, shall we?)
Here’s an internal screenshot of one of the web pages in Statosphere, circa 2006. Yes, it’s a PivotTable rendered by Excel Services (that is, the web version of Excel), “wrapped” in a custom web page:
Figure 2.1
Note
Notice the heavy usage of report filters—Season Number, Season Half, etc.? At that point in time, report filters were available, but slicers didn’t yet exist.
|
As part of this project, I conducted focus groups with sports fans to see what they thought of Statosphere in action. I learned two very important things from those focus groups:
-
•
Most human beings are not terribly interested in data.
Despite being a relatively intellectual group of people collected from the Seattle area, and despite the fact that they were all NFL fans, most participants in those groups exhibited no curiosity whatsoever when presented with the world’s most expensive and amazing sports stats tool.
-
•
Report filters scare most people to death.
Every time we would click a report filter’s little down arrow and a dialog popped up, I could see people’s faces sink into frowns. They just
hated
report filters! This was shocking to me. I had spent the prior two years working on making PivotTables “friendlier,” and not once during those two years had we imagined that report filters were a problem. We had always assumed that they were the easiest part of Pivots, and we hadn’t bothered to test them like this.
The first of these observations has been reinforced for me dozens of times since that fateful day, and it carries tremendous relevance for
everyone
reading this book. You, my dear friends, are data people. And for every one of “us,” there are about 15 people who are
not
data people. Everyone else wants to make better decisions and be better
informed
by data. But they do
not
want to get their hands dirty with data. They do not want to touch data, and they do not want to touch data tools. This is good news! Our jobs exist, in large part, because the rest of humanity has no interest in doing what we do. And your future success hinges, in part, on how completely you internalize this fact: Your colleagues largely want (need!) you to “dumb data down” for them. That does not mean your colleagues themselves are dumb, of course. It just means they lack the interest that powers us, and they need us to take the edge off.
This brings us to the second of my observations. Even something as simple as this is too much “getting dirty with data” for the consumers of our work to tolerate:
Figure 2.2
This seems pretty innocuous, doesn’t it? But when you look closely, well, your colleagues’ discomfort comes into focus: What starts out as a drop-down turns into a dialog, which in itself is an ugly surprise. Then there’s a
treeview
, which itself starts out fully collapsed, of course, so the contents are hidden. Is it clear that you are supposed to click on In Progress and then OK if you want to open the treeview? Nope. You want to multi-select? Okay, there is a check box for that, but users may not figure that out.
This “deconstruction” of the usability of report filters may seem like overkill. All you really need to remember is that things that seem simple to you often have many nasty little sharp edges—edges that are invisible to you, as the shortcomings of report filters were to me before I conducted those focus groups.
Every
interactive report/dashboard you create runs the risk of being hard for some people to use. Always try to keep your mind open to this fact.
In parallel to the focus groups, I also demonstrated Statosphere to Francois Ajenstat, who was then in charge of Business Intelligence Marketing at Microsoft (and now holds a similar position at Tableau). He liked it but famously said he thought it needed to be more “Fisher-Price” in order to appeal broadly. (Fisher-Price is a manufacturer of toys for infants and toddlers.) I’ve been using that phrase—“Let’s make it more Fisher-Price”—ever since.
Statosphere was a complete (and expensive!) failure. But for me, it was a turning point, and a priceless experience that informed
many
future efforts, including slicers themselves. Which brings us to the second part of this story.
Part II:
After Statosphere was canceled (and, indeed, the entire fantasy football team was disbanded and folded into Bing), I started looking for a new job. Magically, as if he were reading my mind, Amir Netz (the architect behind Power Pivot) sent me an email around that time, asking if I wanted to look at a super-secret project they were working on in SQL-land—something they had code named Project Gemini.
Project Gemini was Power Pivot, of course, before it was officially named. I was hooked immediately and became one of the first handful of Microsoft engineers to work full-time on the project.
Amir and I spent the next two years in a state of “creative combat.” It was emotionally charged at times, but primarily it was simply the pursuit of creating a great product. I now know that the product is better as a result of the battles I lost
and
the battles I won.
One of the key debates in the early phases of the project was whether we should rely on Excel’s built-in charting and pivoting capabilities as the visualization layer of Power Pivot or whether we should build a completely new visualization layer, complete with its own charting and pivoting capabilities.
I was staunchly in favor of the “use existing” tactic, primarily because I knew how much work that would be. Teams at Microsoft that build “engines” (like the SQL team) tend to grossly underestimate the engineering cost of building user experiences. And teams that build user experiences (like Excel) return the favor and grossly underestimate the cost of building engines.
As part of our pro/con analysis of “use existing” versus “build new,” Amir and I sat down one day to make a list of weaknesses of Excel’s existing chart and Pivot capabilities. We both expected to emerge with a long list. We emerged, instead, with merely one item: Excel lacked
slicers
. Report filters just weren’t enough.
It was Amir, not me, who pointed out the lack of slicers as a key weakness. But it resonated with me
immediately
, thanks to my experiences on the Statosphere project. We were instantly in agreement. A few days later, we were pitching the Excel team on the idea of loaning them the engineers required to implement slicers. They miraculously agreed, and the rest is history: We got slicers
and
a well-integrated experience that appeals to tens of millions of “Pivot heads” worldwide.
So what are the takeaways from this Part II?
-
•
Slicers are the single biggest “missing link” in Excel’s “interactivity” history.
When you need interactivity in your dashboards, slicers should be one of your first and only thoughts.
-
•
Slicers were originally intended merely as a replacement for report filters.
This, in hindsight,
comically
understates their usefulness, as you will see in the later sections of this chapter.
For more on the topics in this introduction, see the following links:
-
•
http://ppvt.pro/GreatFootballProject
A series of blog posts, starting in 2009, in which I re-implement Statosphere using Power Pivot—my first “serious” work in Power Pivot.
-
•
http://ppvt.pro/ReImplementGFP
A two-part article on how Power Pivot radically improves the BI process, as demonstrated by re-implementing Statosphere using Power Pivot.
-
•
http://ppvt.pro/FFLMadeMyCareer
The story of how fantasy football was “the gateway drug to data” that launched my career.
-
•
http://ppvt.pro/CultOfTheRightThing
A description of that “creative combat” nerd culture from Microsoft, how essential it was, how it got diluted over time, and why I think that is one of Microsoft’s biggest “downfalls.” This is one of the all-time most popular posts on PowerPivotPro.com.
And now for our favorite slicers-related techniques, ranging from “just plain useful” to “mind-stretching inspiration.”
“Initializing” Slicers That Contain Too Many Values to Scroll
Have you ever had a slicer like this? This customer slicer is big, displaying dozens of customers’ names, and we’re not even out of the “Aarons” yet:
Figure 2.3
What do you do? Do you make the consumers scroll the slicer endlessly, in search of the customer they need to see? That would
not
be “Fisher-Price”! Instead, in the Customers table in the Power Pivot window, you can add a simple calculated column called Last Initial:
Figure 2.4
Then you add that field to the Pivot as a Last Initial slicer. When the consumer clicks D, the Customer slicer is instantly limited to customers with last names beginning with
D
, as shown here:
Figure 2.5
If you want, you can then go back and add a First Initial column and slicer, too:
Figure 2.6
Finally, you might want to turn off cross-filtering for the First Initial and Last Initial slicers; you really only need it on for the FullName slicer, and enabling cross-filtering for the initials will slow down your report.
Note
http://ppvt.pro/SLICERPERF
for details on cross-filtering.
|
Wingdings and Other Symbolic Fonts in Slicers
Someone planted an idea in my (Rob’s) head one day: “Hey, can we use the Wingdings font in slicers?”
Me: “No. Wait. Maybe…50/50 likely. Hmmm…Actually, I’m gonna say 90% likely. Yeah, in fact, why WOULDN’T it work?”
If it had ended there, it wouldn’t have cost me any sleep. But, of course, I was up into the wee hours, poring over every symbolic font in Windows, installing freeware character maps so I could view the “extended” characters in those fonts, writing macros. (More on macros later.)
The Symbolic Fonts Available in Slicers
Here are the symbolic fonts that we found installed on a computer running Windows Vista and Office 2010:
-
• Wingdings
-
• Wingdings2
-
• Wingdings3
-
• Symbol
-
• Webdings
-
• Bookshelf Symbol 7
-
• Marlett
MS Reference Specialty
Note
MS Outlook and MT Extra are also apparently symbolic, but they contain few if any useful characters.
|
Wanna see all the characters you can use from each? Here are the Wingdings 1 through 3 fonts (typeable characters only):
Figure 2.7
Here are the Symbol, Webdings, and Bookshelf Symbol 7 fonts (typeable characters only):
Figure 2.8
Here are the Marlett and MS Reference Specialty typeable characters:
Figure 2.9
Extended Characters
Some of the available fonts contain characters that you cannot type without using the Alt key and a numeric identifier. None of those are included in the images above. In order to browse those characters, you need to use the Character Map tool in Windows. On Windows 7, find it by selecting Start, All Programs, Accessories, System Tools, Character Map:
Figure 2.10
The Character Map tool gives you access to all of the other, non-typeable characters in these fonts:
Figure 2.11
Double-click the character you want and then click Copy. Then you just paste the character into your slicer table (see below).
Tip
You can also create a character map in Excel. Select A1:J255. Type
=CHAR(ROW())
and press Ctrl+Enter. Then format each column from B to J with a different symbolic font. Leave column A as regular letters so you can see what you have to type for each character.
|
Some of the Best Slicer Fonts
Using the Character Map tool, I pored over all eight fonts, looking for sets of characters that would be potentially useful on slicers. Here are some sets that I found interesting:
Figure 2.12
Note
You
cannot
mix and match characters from different fonts! If you could, that would be even more amazing. But you have to use only one font on a given slicer. You can use different slicer fonts in a single Pivot, but still only one per slicer. (More on this later.)
|
Some notes on the fonts for slicers shown in Figure 2.12:
-
• Wingdings contains clock symbols for all 12 hours.
-
• The two characters from Symbol would be useful in some geeky workplaces to denote total and percent change. You could use them with
branching measures
that switch between total and percentage change in response to slicer clicks.
-
• Webdings line 3 would be useful to denote sectors, like residential, government, and commercial.
-
• Webdings line 4 would be useful to denote suburban, urban, and rural.
-
• Webdings line 8 shows more weather symbols than you would ever need.
-
• Webdings last line gives you ways to represent planes, trains, and automobiles (and the unmade sequel
Buses, Bikes, and Boats
).
How to Add Symbols to Slicers
To add symbols to slicers, you first make an Excel worksheet with a bunch of symbols from your favorite fonts pasted into columns:
Figure 2.13
Then you copy them and paste them into the Power Pivot window by selecting Home, Paste:
Figure 2.14
You repeat this until you have them all pasted over as new tables:
Figure 2.15
Adding the Slicers
When you add slicers on those fields, you get:
Figure 2.16
What happened to the symbols? By default, slicers don’t use the font you specified, and you see the equivalent characters from Calibri, the default font in Excel. So you’ve got to switch those fonts next.
Changing Slicer Fonts
Changing the font in a slicer is a bit trickier than you might think, but it’s not awful. You
cannot
change the default slicer styles, so you need to instead make a copy of a slicer style. The copy becomes a custom style, which you
can
modify. To do this, select a slicer, right-click in the ribbon, and select Duplicate:
Figure 2.17
Then give your new slicer style a name and click OK:
Figure 2.18
This is
very
important: Your slicer is still using the old, built-in, nonmodifiable style, so you now have to switch it to using the new style, like this:
Figure 2.19
Warning
This is an important step! If you skip this step, the trick won’t work.
|
To modify the custom slicer style, right-click your custom style and choose Modify:
Figure 2.20
Now you get the following dialog:
Figure 2.21
You have some clicking to do: You need to select all eight of these elements. If you don’t change the font in all eight, your symbolic font won’t show up all the time.
Note
This process is tedious, but Microsoft has the right idea here. While you would never want different fonts when you hover, you might want different colors, or maybe bold or italic.
|
Here’s the result of all that clicking:
Figure 2.22
Using One Custom Style per Font
If you plan to use more than one symbolic font in a single workbook, you need to define a custom style for each of the fonts by repeating the steps above.
Note
Whenever you find yourself doing tedious work like modifying eight slicer item style elements, and then doing it all over again for other fonts, it may be time for a macro. For more information, see the next trick in this chapter.
|
Turning Off the Header
In some cases, like this, the symbols are clear enough that you may not need to use a header on the slicer:
Figure 2.23
You turn off the header by deselecting Display Header in the Slicer Settings dialog:
Figure 2.24
Note
Removing the header also removes the “clear filters” button. Trade-offs. Use this option carefully, as it may not always be clear to the report consumer what is going on.
|
Using Macros to Change Slicer Fonts
You just saw how to use Wingdings and other symbolic fonts on slicers for an interesting effect. But there’s a lot of manual work—eight repetitive steps—involved with changing slicer style settings in order to accomplish that. This trick provides a macro that makes that process much more painless.
Note
Even if you’re not a macro person, you should try out macro recording. There’s some “how to” info at the end this post:
http://ppvt.pro/PIVOTMACRO
.
|
The ActiveSlicer Object
The one “magic ingredient” that macro recording does
not
provide is the
ActiveSlicer
object. This bad boy tells you which slicer is currently selected. Without it, you can’t detect which slicer is detected, and many macros are based on knowing the name of a slicer, which is tedious to get. Macros for things like turning off cross-filtering are much easier to use when you can just select the one you want in Excel in order to run the macro.
And Now…The Macro
Here’s the macro you need to use to change the slicer fonts, with the part that you edit in order to control the font boldfaced. (Note that each comment starts with a
'
.) Just paste this macro into the VBA macro environment:
Sub ChangeSlicerFonts()
Dim oSlicer As Slicer
Dim sStyle As String
Dim sFont As String
Dim iFontSize As Integer
' Set your desired font and font size here
' Spelling errors will not cause error,
' but also won't take. Lost 30 min once
' to that problem, so don't be like me!
sFont = "Wingdings"
iFontSize = 24
' This slicer MUST have a custom style
' The built-in styles are read-only and
' CANNOT be modified*****.
Set oSlicer = ActiveWorkbook.ActiveSlicer
sStyle = oSlicer.Style
' The 8 elements to change are conveniently
' numbered 28 to 35. Using this instead of
' xlSlicerHoveredUnselectedItemWithData etc.
For i = 28 To 35
' Set the font
With ActiveWorkbook.TableStyles(sStyle). _
TableStyleElements(i).Font
.Name = sFont
.ThemeFont = xlThemeFontNone
.Size = iFontSize
End With
Next i
End Sub
Note
This macro changes all slicer fonts
except
the header font. This is pretty specifically useful in the Wingdings case and not what you’d use to modify the font of the entire slicer.
|
If You Are Changing the Header Font, Too…
Remember that in this example, you wanted to change just the font of the slicer items, the tiles, and leave the header alone. You didn’t want Wingdings as the caption font; you still wanted it to read as the Region slicer, for instance. And that’s why you had to take eight manual steps. If you instead want to change the header font, too, you can just select Whole Slicer and avoid the eight-step process:
Figure 2.25
A Simple Trick for Combatting “Stale” Slicers
What do you do when your dashboard is refreshed, but its slicers are stuck in “yesteryear”? For example, two weeks of refreshes later, this report still thinks November 15 is what everyone wants to see first:
Figure 2.26
This trick is an oldie but a goodie. And enough folks are now using Power Pivot for SharePoint (PP Server) that this trick’s time has come. And really, it’s relevant on the desktop, too.
On the day when you first made this report, you selected the most recent date (or week, etc.) in the slicer. And you saved the report. All was right with the world! But then, when tomorrow comes, all of your slicers still have that “old” date selected, even after you refresh everything. Ick. Who wants to update all those slicers to point to the latest date? So you might just let the slicers sit on an old date (or week, month, etc.). This forces the consumers of that report to
always
click the latest date, sometimes after scrolling the slicer to the bottom—every time they open the report. They. Don’t. Like. That. And neither would you.
Adding a “Shadow” Column for the Slicer Caption
You need to add a column for the slicer caption. The new column is just a duplicate of the original date column…
except
it’s in the most recent row:
Figure 2.27
This is the formula for that column:
=IF(Calendar[Date] = MAX([Date]),
"Latest",
FORMAT(Calendar[Date],"mm/d/yyyy")
)
This formula checks to see if this row is the “latest” row in the table. If it is, it returns
"Latest"
. If it isn’t, it returns the original
Date
value for this row.
Why do you need the
FORMAT()
function? Why not just use
[Date]
for the “false” part of the
IF()
? Because you get this error if you do that:
Figure 2.28
Because
"Latest"
is text, you need
Date
to also be text. So you use
FORMAT()
to return a text version of the date.
Note
You could also use
[Date] & “”
to “coerce” the date to be text.
|
Now you need to set the column sorting so the slicer sorts appropriately:
Figure 2.29
The new caption column should use the original column for its sort order.
Next, you replace the date field on the slicer with the
SlicerCaption
field.
"Latest"
is the same as 12/2/2013—for now:
Figure 2.30
So far, so good. But then you refresh the data, and now 12/3/2013 appears in your Calendar table:
Figure 2.31
You’ve picked up a new date with the Power Pivot refresh, and
"Latest"
has now marched forward.
Now you refresh the Pivot:
Figure 2.32
When you simply refresh the Pivot, “Latest” is still selected, but now it means 12/3/2013.
Note
If you’re using SharePoint, generally speaking a scheduled refresh combined with this trick will take care of this problem for you. Ditto if you’re refreshing manually on your desktop, for that matter.
|
What if Your Calendar Is Not “Trimmed”?
The example above assumes that your Calendar table is being refreshed and that it contains only dates up until the most recent date—that is, that yours is a “trimmed” calendar.
But you may not have a trimmed calendar. Instead, you may have Calendar tables that go all the way into next year. Some calendars even go to the year 2100. What do you do in such a case? No biggie. Just change your Caption column’s formula to reference the Date column in your data table (maybe the Sales table) instead of the Calendar table:
Figure 2.33
User-Friendly Report Sorting with Slicers
As discussed in the intro to this chapter, making things “Fisher-Price” for consumers is incredibly important. If you aren’t sensitive to this need, sometimes even the greatest analytical work goes unnoticed.
You should always be asking yourself, “How can I make the report easier to understand and use?” As your work becomes more important and makes its way further up the leadership hierarchy of your organization, it becomes more and more critical to get into this habit.
One common consumer need is sorting. Let’s say you have published the following mission-critical Pivot report on UFO sightings in the United States:
Figure 2.34
One of the report consumers says to you, “Great, but how do I sort by average sighting length instead?”
Well, because you’re an Excel pro, you know about this little drop-down, don’t you?
Figure 2.35
This drop-down scares most average users. Really, it does. 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. But remember:
Normal people don’t know this.
Also keep in mind that doing this doesn’t work on SharePoint. And really, report consumer are used to simply clicking on column headers to sort—in just about every single application they have ever used, except for Excel. Even the new Power View add-in for Excel 2013 (see Chapter 6) lets you sort by clicking the header—once for ascending and a second time for descending.
So in cases where sorting is important, can you give consumers something a little friendlier? Yes, you can.
Creating Dummy Tables for Slicers
The first table below lists all the measures you’d like the user to be able to sort by. The second table is just for ascending/descending sort orders, with the names changed to Largest to Smallest and Smallest to Largest because ascending and descending often confuse people:
Figure 2.36
Now you add these tables as slicers on the report, even though they don’t do anything yet:
Figure 2.37
Creating Measures
The next step is to create measures that detect user selections on those slicers:
[SelectedSortMeasure]=
IF(HASONEVALUE(SortBy[Sort Table By]),
VALUES(SortBy[Sort Table By]),
"Total Sightings per Year"
)
[Selected Sort Order]=
IF(HASONEVALUE(SortBy[Sort Table By]),
VALUES(SortOrder[Sort Order]),
"Largest to Smallest"
)
Both of these measures merely return the caption of whatever is selected. And if more than one thing is selected on a slicer, Excel returns the default value
"Total Sightings per Year"
for the first measure.
Next, you create a
1,-1
measure, based on sort order:
[SortOrderMultiplier]=
IF([SelectedSortOrder]="Smallest to Largest",-1,1)
If the
SortOrder
measure defined here returns
"Smallest to Largest"
, then this measure returns
-1
. Otherwise, it returns
1
.
Now you 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 and other times another.
Note
The last line above multiplies
[SortOrderMultiplier]
, which is
1
or
-1
, by the whole thing.
|
Adding HiddenSortMeasure to the Pivot and Sorting
The next step is to add
HiddenSortMeasure
to the Pivot and sort by it:
Figure 2.38
When you sort the Pivot by this measure, notice that it is the negative version of the
Total Sightings per Year
measure. This is expected, based on the slicer selections.
Hiding the HiddenSortMeasure Column of the Spreadsheet
Now you need to hide the
HiddenSortMeasure
column of the spreadsheet. You can either select the column and use Alt+O+C+H or right-click the column header and select Hide, as shown here:
Figure 2.39
Here’s the result:
Figure 2.40
This whole process is actually really easy; it takes longer to read these instructions than to add the sorting to the report.
But what about sorting by state name? We’re glad you asked, because that is the very next trick.
Adding State Alpha Sorting to the Sort-by-Slicer Trick
There are two ways to change the previous trick so that it sorts by state name: using text measures and adding a column to the States table.
Method 1: Text Measures
The first method for sorting by state name involves sorting by
HiddenSortMeasure
, and you somehow have to get the state name reflected in that measure. So you need a measure that represents
StateName
.
It’s not widely known, but measures
can
return text. This measure simply returns the name of the state:
[StateNameMeasure]=
IF(HASONEVALUE(States[FullStateName]),
VALUES(States[FullStateName]),
BLANK()
)
Note
To find out how this formula works, check out
http://ppvt.pro/IFVALUES
.
|
If you add this measure to the Pivot and sort by it, you get this:
Figure 2.41
Well, the measure works. But the sort order is awful. Why is Missouri ahead of Alabama? The next method clears up this problem.
Method 2: Adding a Column to the States Table
Another way to have your slicer sort by state name is to add a column to the States table, like this:
Figure 2.42
Tip
Getting this column into Power Pivot can be a bit tricky, especially with a copy/pasted table. If this were a serious production application, you would want to use SQL as the source for this table and ask your database colleagues to add the column for you.
|
Because you’re working here with UFO data, you paste a second, two-column (StateName and AlphaOrder) States table into Power Pivot and relate it to your original States table. (Be sure to treat the new table as a lookup table!) Then you use
=RELATED()
to add it to your original States table.
Okay, now it’s time for the
AlphaSort
measure:
[AlphaSort]=
MAX(States[AlphaOrder])
This
does
sort properly:
Figure 2.43
Note
You could use
MIN()
,
SUM()
, or even
AVERAGE()
instead of
MAX()
. You just need something that returns the number.
|
Now you just add the
"State Name"
value to your Sort Table By slicer table and add another clause to the
IF()
in the original
HiddenSortMeasure
, and you get this:
Figure 2.44
This is a bit trickier than sorting by the other columns, but it’s doable.
Dynamic Top
N
Reports Using Power Pivot
Check out
this
bad boy:
Figure 2.45
Folks, this combination of slicers and DAX is just amazing. Do you
see
that picture above? Say that a consumer of this report wants to change it completely and instead see the top 10 customers by total paid (sales). It takes two quick slicer clicks:
Figure 2.46
Note
This solution requires the
RANKX()
and
SWITCH
() functions, which were not present in the very first release of Power Pivot, but versions 11.x and higher all have them.
|
At heart, this is really just a variant of the sort-by-slicers trick described earlier in this chapter.
Creating Two Disconnected Slicers
A great technique is to create slicer tables that are only intended to populate slicers, never to be related to other tables, like this:
Figure 2.47
To create these slicers, you create the Measure for TopN and TopN tables manually in Excel and then copy and paste them into Power Pivot:
Figure 2.48
Behind-the-Scenes Measures: Harvesting Selections from Slicers
The next step is to write measures that “harvest” the user’s selections from those slicers:
[Selected TopN Measure] = MIN(MeasureForTopN[ID])
[SelectedTopNNumber] = MIN(TopN[Top])
Note
Here you use
MIN()
to break ties in case the user is silly and selects more than one value on a slicer. You could just as easily use
MAX()
, or do an
IF(HASONEVALUE())
test, catch the case where more than one is selected, and specify a default value.
|
[Selected TopN Value] =
SWITCH([Selected TopN Measure],
1, [Total Paid],
2, [Margin],
3, [Margin Pct],
4, [Orders Placed],
5, [Tax Paid],
6, [Shipping Paid]
)
This measure returns the
[Total Paid]
amount if the user selects Total Paid on the slicer. Cool, huh?
Note
If you have a hard time writing nested
IF
s, rejoice. The Power Pivot team has provided
SWITCH()
, which makes your life much easier!
|
Using RANKX()
Next, you use
RANKX()
with the measure that ranks each customer according to the selected measure:
Figure 2.49
Here’s the formula:
[CustomerRankBySelections] =
RANKX(ALL(Customer[FullName]), [Selected TopN Value], ,0)
Here are a few observations on
RANKX()
:
-
•
It’s amazing...even better than SWITCH().
Although
RANKX()
is a little weird, you can’t live without it.
-
•
Why do we have to specify ALL() in the first param?
We struggled here at first. The function text for
RANKX()
just says “Table” for that first param. So we were throwing
VALUES(Customer[FullName])
in there, and every customer was coming back ranked as 1. This is the right function design if flexibility is your overwhelming design goal. But for the 99% case, this is incredibly counterintuitive. For the Excel crowd, it would be much better to have a simpler function where all you need to do is specify a column—no
ALL()
and no
VALUES()
, just a “raw” column.
-
•
That third parameter is deeply mysterious and very rarely used.
You can leave it blank, and it doesn’t cause any harm. So, um, leave it blank. (A use for that parameter is covered in
DAX Formulas for Power Pivot
, in the chapter on the
X
functions.)
-
•
Set the fourth parameter to 0 when you want the largest number to be ranked 1.
If you omit this parameter, it defaults to the other direction, which seems backward for a function named
RANK()
. (Think of the best team in the NFL each year...is the team’s rank 1 or 32?)
-
•
There is a fifth argument in RANKX() for dealing with ties.
The choices for the fifth argument are
Skip
and
Dense
. With
Skip
, if you have two items tied for #1, they both get a rank of 1, and the next item is ranked #3. This is similar to the decades-old
RANK()
function, which was renamed
RANK.EQ()
in Excel 2010. With
Dense
, if you have a three-way tie for the second position, all three items will be ranked #2, and the next item will be ranked #3.
Caution from Bill
Dense
, described above, is equivalent to the method mysteriously introduced in Excel 2010 PivotTables when you chose Options, Show Values As, Rank Largest to Smallest. It’s fair to say that this
Dense
option is the fourth method of handling ties to be introduced to Excel.
Statisticians will be disappointed that Power Pivot lacks an equivalent to the
RANK.AVG()
function introduced in Excel 2010. With this method, if you have two items tied for #1, they are both ranked 1.5.
Excel pros will continue to be disappointed that no one on the Excel team can fathom the need to have every rank represented exactly once, as shown in column G of this figure:
|
Figure 2.50
Reaction from Rob
What??? Why would I ever want what’s being displayed in column G? (Hrm, I guess I am showing my Microsoft roots here.)
Reaction from Bill to Rob’s Reaction
I thought it was obvious! To sort with a formula, you need a column that returns every rank exactly once to act as the second argument in the
MATCH()
function:
=INDEX(A$3:A$12,MATCH(ROW(1:1),G$3:G$12,0))
. What do the Excel PMs do all day if they aren’t struggling to sort with a
formula?
|
The Should Customer Be Included Measure
At this point, if you were always just doing top 10, and not a slicer-controlled top
N
, you’d be done. You’d just set a filter on the Pivot to always show the top 10 customers by
CustomerRankBySelections
, and everything would be great. (In fact, you could have skipped
CustomerRankBySelections
altogether and just done a top 10 filter by
Selected TopN Value
, the branching measure itself.) But you need one more simple measure:
[Should Customer Be Included] =
IF([CustomerRankBySelections]<=[SelectedTopNNumber],1,0)
Then you filter the Pivot such that only the top
N
are included, via the
IF()
measure. To do this, you filter the Pivot to only show rows where that measure is greater than 0 or equal to 1:
Figure 2.51
Ensuring That Customer Names Are Unique
Some data sets contain a name field that might have different people with the same name. Your data might have more than one customer named Willie Xu, for instance. The approach described in this trick combines those customers into one “super customer” who unfairly sneaks into the top 10. There are a couple ways to avoid this problem. The first is to create a unique customer name and use that on your Pivot:
Figure 2.52
Or, in theory, you can change your
RANKX()
measure to use the behind-the-scenes unique ID instead. But when you use
ALL()
, as in the original measure, it is so slow that the Pivot never finishes calculating, and you’ll end up canceling it:
[CustomerRankBySelections] =
RANKX(
ALL(Customer[CustomerKey])
, [Selected TopN Value], ,0)
When you use
VALUES()
instead, every customer comes back ranked as
1
:
[CustomerRankBySelections] =
RANKX(
VALUES(Customer[CustomerKey])
, [Selected TopN Value], ,0)
So let’s call this a work in progress.
The Readout
To add some class and usability, you might want to create a readout that responds to slicer selections:
Figure 2.53
This readout is just a formula that uses Center Across Selection:
Figure 2.54
The two cells referenced in this formula are interesting. One is a cube formula:
Figure 2.55
Here’s the formula in text:
=CUBEVALUE("PowerPivot Data", "[Measures].
[SelectedTopNNumber]",Slicer_Top)
Do you see how the formula is parameterized by the name of a slicer? This allows the formula to be sliced by the slicer, just like a Pivot.
The other cell is the top cell in the Rows drop zone in a Pivot:
Figure 2.56
This Pivot has a slicer connection established to the same measure-selector slicer, and it also has the same field on rows as the slicer field itself. It has nothing in the Columns or Report Filter or Values drop zones.
Note that there are two alternate ways of accomplishing this without using a “dummy” Pivot. One is to write a measure that returns text and fetch that with a
CUBEVALUE()
formula. The other is to use
CUBESET()
and
CUBERANKEDMEMBER()
to directly fetch user selections from the slicer, which is covered later in this chapter.
Note
For more on text measures, see
http://ppvt.pro/TXTMEASURES
.
|
Conditional Formatting Controlled via Slicers
It’s possible to control conditional formatting via slicers. For example, in this case, at the 65th percentile, model name profits are shaded green (though in this printed book, they appear as a shade of gray):
Figure 2.57
In this case, xxx:
Figure 2.58
Another Disconnected Table Technique
This disconnected table technique is, of course, a recurring favorite. First, you create a single column in Excel, fill it with the numbers that you want to appear on your slicer, and copy that whole column:
Figure 2.59
Then you paste the column as a new table in Power Pivot:
Figure 2.60
Here is the resulting CFMinBar table:
Figure 2.61
Then you write a measure on your new table:
Figure 2.62
This measure harvests the selection from the slicer.
Note
In this case, you could just as easily have used
MIN()
rather than
MAX()
.
|
It looks like this in a Pivot:
Figure 2.63
Using the New Slicer and Measure to Control Conditional Formatting
Now you need to make a real Pivot—one with a useful measure on it:
Figure 2.64
Rather than display
Selected Min CF Bar
on the pivot, you can use a cube formula, off to the side, to capture that measure:
Figure 2.65
Note how this formula references the slicer, too. If it didn’t, it would always return 95%.
Adding a Color Scale Rule
Now you add a three-color scale rule to the Pivot:
Figure 2.66
Then you set it to affect the entire measure:
Figure 2.67
You get this result from the simple three-color scale conditional formatting rule:
Figure 2.68
Now for the Trick
Here’s the sneaky part. Go to Manage Rules:
Figure 2.69
Edit the one rule you have on the Pivot, and you get this dialog:
Figure 2.70
Note the circled buttons. These are RefEdit controls. The thresholds in the conditional formatting rule can be cell references.
You now set the controls to reference the cube formula cells you created previously:
Figure 2.71
And then you hit what appears to be a bug. Click OK, and you get this message:
Figure 2.72
Don’t you love the “Was this information helpful?” link?
Excel is expecting an integer like 90 rather than a percentage value like 0.90, which is what the measure returns. But you get this error message even when you just type the number 90 into a cell and then reference that cell. However, you don’t get the error message when you type 90 directly into the RefEdit control rather than use a cell reference. Something is broken here: There appears to be a bug in Excel 2010 and Excel 2013 conditional formatting.
Correcting the Bug
It seems that only the Percentile option doesn’t like cell references because the Number option works:
Figure 2.73
Also, the Percent option works. If your data is evenly distributed, you can get away with using Percent instead of Percentile. However, if the data is heavily skewed with a few large records, you will want the exactness of using Number.
In order to use the Number option to let the user control percentile-based conditional formatting with a measure like
Profit
, however, you need to get a bit more sophisticated with your measures. If you can get the actual
Profit
value for, say, the 80th percentile model name into a cell, you can reference that. And that’s precisely what those three cells in this image contain:
Figure 2.74
In this figure, each number RefEdit in the Edit Formatting Rule dialog points to a different cell on the sheet.
Here you can see that cell F2 contains a cube formula that returns the measure Profit Required for Green CF:
Figure 2.75
Setting the Green Threshold
Here’s the formula for calculating the
Profit Required for Green CF
measure—the one displayed in cell F2 above:
[Profit Required for Green CF]=
MINX(TOPN([Model Name Rank for Green CF],
ALL(Products[ModelName]),
[Profit]
),
[Profit]
)
The
TOPN()
function returns a set of rows from
ALL(Products[ModelName])
, ranked by the
Profit
measure. But how many rows does it return? The
Model Name Rank for Green CF
measure determines that:
[Model Name Rank for Green CF] =
CEILING(
(1-[Selected Min CF Bar])*
[Number of Model Names with Profit],
1
)
Selected Min CF Bar
returns a value between 0 and 1, such as
0.90
if the user selects 90th percentile on the slicer.
And here’s the other measure:
[Number of Model Names with Profit] =
CALCULATE(COUNTROWS(VALUES(Products[ModelName])),
ALL(Products[ModelName]),
FILTER(ALL(Products[ModelName]),
NOT(ISBLANK([Profit]))
)
)
The purpose of this measure is essentially to tell how many products have actually sold. It weeds out products that are in the catalog but have not sold yet or have not sold recently enough, and so on—depending on slicer selections.
So if you have 100 products that have actually sold, and the user has selected 90th percentile on the slicer,
Model Name Rank for Green CF
becomes:
CEILING(
(1-0.9)* 100,
1
)
which is
10
.
And then
Profit Required for Green CF
becomes:
MINX(TOPN([10, ALL(Products[ModelName]),[Profit]),
[Profit]
)
This grabs the
Profit
value of the lowest-ranked product in the top 10. And this is the value that is returned into cell F2, which is what the conditional formatting rule references! Any product with a
Profit
value greater than or equal to the
Profit
value of the 10th-most-profitable product will be colored green.
Setting the Yellow and Red Thresholds
There are multiple ways to handle the yellow and red thresholds. You could give the users three separate slicers and let them control each one independently. That seems like overkill in this case, and you can instead hard-wire those to be 20% and 40% lower than the green threshold, respectively:
[Model Name Rank for Yellow CF]=CEILING((1-
[Selected Min CF Bar]+.2)*
[Number of Model Names with Profit],1)
[Model Name Rank for Red CF]=CEILING((1-
[Selected Min CF Bar]+.4)*
[Number of Model Names with Profit],1)
Then the rest of the measures are the same for both red and yellow as they were for green.
Catching Slicer Selections in a Formula
A lot of people want to catch slicer selections in formulas. It’s actually very simple to catch users’ slicer selections in Excel formulas, if you are using Power Pivot (aka the 2013 data model feature):
Figure 2.76
Sometimes you want to catch a user’s slicer selections and use them in a measure. Other times you want to catch them and use them in “normal” Excel formulas in a worksheet. Here we examine the latter option.
Using a CUBESET() Formula
The first step is to write a single
CUBESET()
formula, as shown here:
Figure 2.77
In Power Pivot v1 and v2, the first argument is always
PowerPivot Data
. In Excel 2013, it’s
ThisWorkbookDataModel
.
The second argument is the “formula-approved name” for the slicer. To see it, click the slicer and check its Slicer Settings dialog:
Figure 2.78
The third argument can be left blank, but then you get a blank cell in your sheet. If you set it to
"This is My Set"
, as shown here, you can see where the set lives.
Using a CUBERANKEDMEMBER() Formula
Next, you write a
CUBERANKEDMEMBER()
formula, wrapped in an
IFERROR()
:
Figure 2.79
You then fill this formula down enough cells to handle every slicer tile (about 26 cells down in this case).
Note the following:
-
• You use
IFERROR()
to handle the case where the user has not selected enough slicer tiles.
CUBERANKEDMEMBER()
returns an error when you “fall off the edge” of the user’s selections.
IFERROR()
returns a nice blank cell instead.
-
•
ROW(A1)
returns
1
, and when you fill the formula down, you get
ROW(A2)
,
ROW(A3)
, etc., so that each cell grabs the first, second, third, and so on values from the set of user selections.
-
•
$M$1
is the cell where you create the
CUBESET()
formula, and you do not want that reference to autoadjust.
Applying More Formatting Rules
Now you can use other formulas to concatenate all selections into a single, comma-separated string. Or you can apply conditional formatting to the cells so that they “light up” when there’s a selection. In this case, you can leverage the “unique values” flavor of conditional formatting, since all slicer tiles always have unique names:
Figure 2.80
Note
You need to make sure you always have at least two blank cells in your conditional formatting rule. If you don’t, when the user selects all slicer tiles except one, the single blank
CUBERANKEDMEMBER()
formula
will
be formatted.
|
Dealing with No Selection
If the user makes no selection, you get this:
Figure 2.81
This is probably okay in most cases.
If it’s not okay, and you want to show all individual values “lit up” instead, you can use an alternative trick to fetch each individual value. We won’t go into that here in great depth, but these steps will get you started:
1. Establish a second
CUBESET()
formula and rather than use the name of the slicer as the second argument, use something like
"[
TableName
].[
ColumnName
].[All].children"
(including the double quotes and substituting the name of the table and column that the slicer is based on).
2. Establish a second series of
CUBERANKEDMEMBER()
formulas that reference the second
CUBESET()
cell.
3. Now wrap your original
CUBERANKEDMEMBER()
formulas with an
IF()
to detect the case where the first
CUBESET()
returns
"All"
for its first member, which happens only when the slicer is cleared of any selections.
4. When you get
"All"
from that first
CUBESET()
, switch over to using a cell from that second series of
CUBERANKEDMEMBER()
formulas (the ones established in step 2).
Here’s an example of a formula for step 3:
IF(CUBERANKEDMEMBER(<original CUBESET cell>,1)="All",
<relative ref to cell in the 2nd series
of CUBERANKEDMEMBER cells>,
<original CUBERANKEDMEMBER formula>
)