Chapter 1: Dashboard Tricks and Visualization Techniques
Broadly speaking, Power Pivot is a numbers-producing machine: Raw data goes in, your formulas and relationships digest it, and magically useful numbers come out. Those numbers are often metrics on a business, and that’s a beautiful thing; very often, those metrics are being “seen” for the first time in the history of the business. Quite often, before a business adopts Power Pivot, it is forced to operate without metrics that are, in hindsight, quite clearly critical . This is a lot like a doctor suddenly having access to patient information such as temperature, pulse rate, and blood pressure—after lacking that information for most of a career. Such a shift is transformational.
So, Power Pivot produces numbers—incredibly important numbers that quite often have never before existed. It’s empowering stuff for sure.
There is a natural tendency among “numbers” people to view the freshly pressed numbers as the final destination. Resist that temptation! In order for numbers to have an impact, they need to be communicated to other people who may not be fascinated with these magical digits. Furthermore, those people then need to translate them into action. So the way you present numbers is often every bit as critical as the numbers themselves.
This chapter provides a collection of tricks in that vein. We have intentionally selected techniques ranging from “bread and butter” (that you might use in nearly every report/dashboard) to “envelope pushing” (that you might not apply in precisely the manner presented but that might inspire related approaches). We start with one of the former.
Adding a “Last Refreshed Date” Readout
You’ve built some killer models and reports. You’ve published them to SharePoint. You’ve scheduled automatic refresh to run, say, once per week. You have this Power Pivot thing All. Dialed. In. But are the report consumers satisfied? Nope, they aren’t satisfied!
You can be certain that your report consumers will never be satisfied. And, hey, that’s kinda cool. It’s a good thing. You weren’t given all this new power just so you could sit on your laurels, were you? Nope. True Power Pivot pros aren’t even sure they can find their laurels, much less sit on them. You will always be improving—both your skill set and the reports you produce.
One improvement you can make is to automatically inform report consumers of how “fresh” the data is so they don’t have to deal with stale reports. Adding such a readout is actually pretty simple: It requires just two steps.
Step 1: Adding a LastRefreshed Measure
Somewhere in the Power Pivot window, you probably have a column whose most recent date is always the date on which the data was last refreshed. For instance, in a retail system, you might be able to use your Sales table for this, as long as you always have at least one transaction per day. Or perhaps the Calendar table you pull from the database is always current (and does not contain future dates). Or perhaps you can get your DB admin to add a single-cell table just for this purpose.
This example uses the TransactionDate column from a Sales table:
Figure 1.1
Did you know that measures can return dates? They sure can, and it’s killer useful. Here’s how you create a measure that does this:
[LastRefreshed] = LASTDATE(Sales[TransactionDate])
LASTDATE() is kinda like MAX() but for dates. It always returns the most recent transaction date:
Figure 1.2
Neat, huh? It’s a date returned as a measure, in a Pivot.
Step 2: Using the Measure in a Cube Formula
The most flexible, least intrusive way to display the LastRefreshed measure in a report is to create a single cube formula for it and then stuff that formula into a single cell. That way, you have complete control over its appearance.
Two things to note here:
To make a long story short, here is the cube formula to use in this case:
="Last Refreshed: " & TEXT(CUBEVALUE("PowerPivotData",
"[Measures].[LastRefreshed]"),"mm/dd/yyyy")
You can leave the result as a date serial number and then put the label in the custom format. Use =CUBEVALUE() to return the date serial number. Select the cell, press Ctrl+1, choose Number, Custom. In the Type box, enter “Last Refreshed “m/d/yyyy:
Figure 1.3
Plop this in the desired cell, and you’re all set:
Figure 1.4
Pretty slick. The cell will refresh every time the workbook is refreshed. But it will not be reevaluated during an update, as happens with a slicer.
Note
For more on refreshing versus updating and the huge difference in performance between the two, see
http://ppvt.pro/REFRESHUPDATE .
Normalizing Your Measures to First/Average/Max Values for Charts
In this chart, the line for Total Sales is quite visible, whereas the other two lines are squashed at the bottom. Even when this chart is in color, you can’t tell that the line for Transaction Size is 50 times higher than the line for Active Customers.
Figure 1.5
The three lines plotted on this chart are “sourced” from the following numbers:
Figure 1.6
These three measures are very different in their relative sizes, varying from two digits to six digits. Data like this results in crappy charts. But with a little formula magic, you can fix the chart above so it looks like this:
Figure 1.7
Ah, formulas make everything better. Yep, this chart uses the same data as the earlier chart, just “normalized.”
A Word from the Charting Pit of Derision
Yeah, I (Rob) hear that chittering out there...the mandibles of the demonspawn chart fiends are clacking out a sound that resembles “Secondary Axis!”
Well, I need three axes this time. Is there a Tertiary Axis feature? I seriously don’t even know. There are only two sides to the chart, so it would make sense, I guess, to not offer a tertiary axis. You’d have to start “stacking” scales side by side, and that would probably make Tufte cry.
But I’m intentionally not checking whether there is such a feature. Because honestly I don’t even like the Secondary Axis feature that much.
If you are absolutely sure that none of your numbers will ever be 0 or negative, you could select Layout, Axes, Primary Vertical Axes, Show Axis with Log Scale:
Figure 1.8
This will automatically make the smaller numbers easier to see. In a log scale, the distance from 1 to 10 is the same as the distance from 10000 to 100000. However, when, in real life, would you have a data set without a single zero point?
Numbers Are Numbers, Visuals Are Visuals, and Everyone Has a
Preference
I (Rob) have a theory that you are either a numbers person or a visuals person. When you first get some new data, is your first instinct “I need to crunch this data with some formulas,” or is it “I need to get this on a chart”?
Now, of course, a numbers person uses charts, and a visuals person sometimes needs to write some formulas. But which one is your first instinct determines which type of person you are.
I’m a numbers guy. Charts are very much a last step in the process—if that—in my world. Heck, give me some conditional formatting in a Pivot, and I am usually set. (Tellingly, though, I always need conditional formatting before I am happy. See, even a numbers guy can leverage visuals; it’s just that I am more on the numbers side of things.)
So, even in a case with two different measures, I am tempted to correct with formulas rather than track down the Secondary Axis feature. I’m really just not that comfortable with charts. I struggle to make them do what I want. And I want to work with numbers. So this is really all just personal preference on my part.
The Formulas
Let’s get back to normalizing the earlier data. Basically, you divide each measure by the maximum value of that measure and put everything on a 0 to 100% scale. Here’s one of the formulas you need to do this:
[Sales Indexed to Max Week] =
[Total Sales Measure] /
MAXX(ALL(Calendar[WeekNumberOfYear]), [Total Sales Measure])
You put Calendar[WeekNumberOfYear] in the Rows drop zone for the Pivot (which “powers” the horizontal axis of the PivotChart). So if you change the field in the Rows drop zone, you need to change that part of the formula.
The other two measures follow exactly the same pattern, but you substitute their respective base measures for Total Sales Measure .
In this Pivot, all three sets of data have been normalized to be between 0 and 100%—which yields a useful chart:
Figure 1.9
Alternative Formulas: Using the Average
Maybe 0 to 100% is too restrictive for you. Maybe you want to divide by the average instead of the max. Here’s what it looks like:
[Sales Indexed to Average Week] =
[Total Sales Measure] /
CALCULATE([Total Sales Measure] /
DISTINCTCOUNT(Calendar[WeekNumberOfYear]),
ALL(Calendar[WeekNumberOfYear)
)
Note
Again, if you have something else in the Rows drop zone, you need to replace Calendar[WeekNumberOfYear] .
This yields the following, slightly different (better?) chart:
Figure 1.10
The chart is still quite readable, but the fact that Active Customers has wider variation than the other measures is no longer hidden by being squashed into 0 to 100%.
Of course, you could also just use AVERAGEX() instead of those CALCULATE() shenanigans in the denominator. In fact, that’s what does happen for Trans Size Indexed to Average Week :
[Trans Size Indexed to Average Week] =
[Transaction Size] /
AVERAGEX(ALL(Calendar[WeekNumberOfYear]), [Transaction Size])
It doesn’t make a huge difference in most cases. AVERAGEX() doesn’t care whether certain weeks had higher sales volumes than others: All weeks will be averaged as equals, whereas the fancy CALCULATE() approach above computes the average as a grand total ratio, which is inherently weighted.
Building Charts That Are Dynamically Indexed to the First Value
Try using normal Excel to create a sliceable chart in which every series always begins at 100%. A chart like this is very useful for comparing the relative performance of different things over a given period of time. Setting this up is relatively labor intensive in regular Excel, and then if you want to change the selected time period, you must repeat that manual effort.
Power Pivot allows you to create such a chart with less effort than required in normal Excel, and the resulting chart responds dynamically to changing date range selections, with no modifications required.
In the following example of such a chart, notice how September is selected in the first image and October is selected in the second image. In both cases, all three series are indexed to 100% at the beginning of the time period:
Figure 1.11
Figure 1.12
Here’s the formula for the normalized measure displayed on the chart:
[Normalized Sales] =
DIVIDE([Total Sales], [Sales on First Date in Range])
This seems anticlimactic, doesn’t it? But there’s real magic in the Sales on First Date in Range measure:
[Sales on First Date in Range] =
CALCULATE([Total Sales],
          DATESBETWEEN(Calendar[Date],
                      [First Date in Range],
                      [First Date in Range]
                      )
)
Sorry, we keep teasing you. We like to write measures in intermediate steps like this. The real magic is in that First Date in Range measure:
[First Date in Range] =
CALCULATE(FIRSTNONBLANK(Calendar[Date], [Total Sales]),
          ALL(Calendar[Date])
)
Note
We struggled, at first, to write this measure. We tried using
ALLSELECTED(Calendar[Date]) , but because we were slicing by the Month column (a different column than Date) and letting Month filter the date range rather than filtering by the Date column directly,
ALLSELECTED(Calendar[Date]) was returning January 1 even when we had selected December on the month slicer. VALUES() provided another dead end. We found ourselves desiring an ALLVALUES() function before realizing that FIRSTNONBLANK() is built for this kind of thing. All’s well that ends well.
Bubbling Up Exceptions with “Sarah Problem”
Sometimes, when a Pivot has multiple fields in the Rows drop zone or the Columns drop zone, interesting “outlier” values are hidden from view until the user expands (drills down into) the correct branch of the Pivot. Rather than require consumers of your Pivots to expand and scan every node, it is sometimes valuable to “bubble up” certain details from lower levels and display them at a higher level.
For example, in the following Pivot, a flag appears at the top level (Accessories), which tells you there’s a problem further down. You then expand Accessories, and then Bottles and Cages, to find that Road Bottle Cage—a product with significantly negative 1-year sales growth—is the culprit:
Figure 1.13
At the top level, the Accessories category looked quite healthy, so you may never have known there was a problem lurking deeper down. You can use “bubbling up” techniques to prevent such lurking problems. We like to call this particular example the “Sarah Problem” technique.
Starting with a “Sarah Problem” Measure
Let’s say you have a measure. It doesn’t matter what it is or how it’s calculated really, except that it reports on whether there’s a problem. It returns 1 (or Yes or True ) if there’s a problem, and it returns 0 (or No or False ) if there isn’t. Or maybe it returns a “regular” number when it crosses a certain line that your business has decided is bad. The measure helps you determine where there’s a problem, and we punnily call it Sarah Problem. (To pronounce it properly, you have to add a question mark, so, technically, it’s Sarah Problem?)
Note
I (Rob) can’t take credit for the Sarah Problem pun. Back when I lived in Seattle, my wife (girlfriend at the time, and fellow Microsoft engineer) played roller derby. She was a “Rat City Rollergirl,” playing for a team called Grave Danger, and her skater name was Natalie Fatality. And she had a teammate whose skater name was “Sarah Problem.” I am not making this up.
Back to the Formulas
In this example, the story of Sarah Problem starts with a measure named 1 Year Sales Trend . The formula for 1 Year Sales Trend is completely irrelevant! You just need to know that it measures “year-over-year” sales growth or decline. So it can go as low as –100% (for example, in the case of products that haven’t sold at all this year) and can go as high as, well, there is no upper limit.
Say that you’ve decided that anything that falls below –10% is bad. So you want to flag products that have declined by 10% or more.
Here you see the 1 Year Sales Trend measure circled:
Figure 1.14
Notice that Sarah Problem is very meticulous! At the Accessories level, the trend is very positive, but it’s still flagged:
Figure 1.15
How do you create that Products in Decline column of the Pivot? Well it starts with another measure:
[Products in Decline] =
COUNTROWS(FILTER(Products, [1 Year Sales Trend]<-.1))
This formula counts the number of rows in the Products table (each of which is an individual product) for which 1 Year Sales Trend is below the threshold of –10%.
Here’s how you do the conditional formatting:
Figure 1.16
This conditional formatting rule is pretty simple: It just flags cases where there’s at least one “bad” product. (Note that the Show Icon Only check box is checked.) So you get a flag whenever there’s at least one “bad” product, in any level of the Pivot.
Of course, maybe you don’t want to count bad products. Maybe you want to count bad stores. Or bad customers. To count anything, you just change your second measure to count rows of the proper table. Or maybe even distinct values of a column, using VALUES( column ) . Knock yourself out. But know that no one knocks people out like Sarah Problem.
Ranks and Exceptions That Bubble Up to Subtotals
Rather than bubble up the existence of problems or outliers, what if you want to display the best (or worst) value that can be found deeper down a particular branch of a Pivot?
Here’s an example of bubbling up the best product rank under a branch:
Figure 1.17
Here’s the measure formula:
[Product Sales Rank]=
IF(HASONEVALUE(Products[ProductName]),
RANKX(ALL(Products), [Total Sales]),
MINX(VALUES(Products[ProductName]),
RANKX(ALL(Products), [Total Sales])
)
)
The first input to IF() (that is, HASONEVALUE ) checks whether the current measure cell is in the context of a single product. If it is, the first branch performs a rank of that product against all other products:
Figure 1.18
So really, the first branch is just a normal RANKX() measure.
The second branch of the IF() is the nifty branch:
IF(HASONEVALUE(Products[ProductKey]),
RANKX(ALL(Products), [Total Sales]),
MINX(VALUES(Products[ProductKey]),
RANKX(ALL(Products), [Total Sales])
)
)
So if the current measure cell is not a single product, the second branch “fires”:
Figure 1.19
The boldfaced part of the formula basically does this: For each product that is “valid” in the current measure cell, it finds the product’s rank among all the products. Then it returns the lowest such rank from all those products.
So It’s Not Really “Bubbling Up” but Working Overtime
No measure cell ever impacts another measure cell. Each measure cell is calculated independently, as if it were an island.
In the case of the Product Sales Rank measure, the darker-shaded subtotal cells of the Pivot (the ones where there is more than one product “active” in that context) force the Power Pivot calc engine to work much harder than it does for the unshaded (single-product) cells:
Figure 1.20
So, in essence, every product in the Products table is ranked three times: once at each level of the Pivot. Don’t be shocked if this sometimes results in slow Pivots. In fact, whenever you see one X function, like RANKX() or MINX() , nested inside another X function, that’s generally a clue that you may see slow slicer clicks, since each X function is itself a “go do something a bunch of times” machine.
Custom ToolTips in Dashboards
It’s possible to create custom “on hover” ToolTips on each cell in a dashboard. Here’s an example:
Figure 1.21
Note
You could use the Input tab of the Data Validation dialog box to specify a ToolTip that appears when you select the cell. The ToolTip shown here is better, though, because it appears on hover.
The Trick: Hyperlinks to Nowhere
This trick involves a guerilla-style hack. You select a cell, choose Insert Hyperlink, and set the cell reference to the cell you just selected, like this:
Figure 1.22
Your hyperlink in cell D5 goes to cell D5, making the hyperlink a “do nothing” link.
Now click ScreenTip and enter a ToolTip:
Figure 1.23
You now have an “on hover” ToolTip for cell D5!
Building a Better ToolTip Process
The problem here is that this process is incredibly tedious. Who wants to go through this process for each cell? Blech.
To make it easier to create another ToolTip in future, you can create a “mirror” sheet that has the same shape and location as the dashboard. In it, you enter other desired ToolTips:
Figure 1.24
Next, you create a macro called RunTheToolTipHack() :
Sub RunTheToolTipHack()
CubeFormulasToolTips ActiveSheet.Name, "ToolTips"
End Sub
Go back to your dashboard sheet, make sure your mirror sheet is named ToolTips, and run this macro. It calls these two other macros that you will also need to add to your workbook:
Sub CubeFormulasToolTips(sSheet As String, sToolTipsSheet As String)
Dim oMainSheet As Worksheet
Dim oTipsSheet As Worksheet
Dim oRange As Range
Dim c As Range
Dim sFormula As String
Dim sAddress As String
Dim sToolTip As String
Set oMainSheet = Sheets(sSheet)
Set oTipsSheet = Sheets(sToolTipsSheet)
Set oRange = oMainSheet.UsedRange
For Each c In oRange.Cells
sFormula = c.FormulaR1C1
sAddress = c.Address
sToolTip = oTipsSheet.Range(sAddress).Value
If Left(sFormula, 5) = "=CUBE" Then
SetHyperlink sSheet, sAddress, "'" & sSheet & "'!" _ & sAddress, sToolTip, True, False
End If
Next
End Sub
Sub SetHyperlink(sSheet As String, sCell As String, sDestAddress As String, sToolTip As String, bFormula As Boolean, bLookLikeLink As Boolean)
Dim oSheet As Worksheet
Dim sFormat As String
Dim c As Range
Dim iColor As Integer
Set oSheet = ActiveWorkbook.Sheets(sSheet)
Set c = oSheet.Range(sCell)
sFormat = c.NumberFormat
iColor = c.Font.ColorIndex
If bFormula = True Then
oSheet.Hyperlinks.Add Anchor:=c, Address:="", _
SubAddress:=sDestAddress, ScreenTip:=sToolTip
Else
oSheet.Hyperlinks.Add Anchor:=c, Address:="", _
SubAddress:=sDestAddress, _
TextToDisplay:=c.Value, _
ScreenTip:=sToolTip
End If
c.NumberFormat = sFormat
If bLookLikeLink = False Then
c.Font.Underline = xlUnderlineStyleNone
c.Font.Color = iColor
End If
End Sub
The RunTheToolTipHack() macro “grabs” the ToolTips from your mirror sheet and assigns them all to the cells on your main sheet. It’s magic.
Questions About This Trick
We’re guessing you may like this hack and yet still have some questions about it:
Named Sets and “Asymmetric” Pivots: Showing Different Measures for Different Years
Here’s a good challenge: How do you take the first Pivot below and turn it into the second one? This is one of those cool little (and simple!) tricks that we all need from time to time.
This Pivot has three measures ( Total Sales , Sales to Parents , and Sales to Married Couples ) displayed for each year from 2001 to 2004:
Figure 1.25
But you want the next Pivot, which displays the Sales to Married Couples measure up through 2002 but “discontinues” the display of that measure in 2003, replacing it with the Sales to Parents measure:
Figure 1.26
You cannot just filter out a measure in some places. A measure is either on the Pivot, or it’s not. Does that mean you’re stuck? Nope, you just need to use a different feature. You can create a set based on row (or column) items:
Figure 1.27
That hidden gem on the Options tab opens this dialog:
Figure 1.28
When this dialog first came up, it had more rows in it. We’ve already deleted some of the rows, like (2001, Parents) and (2003, Married). When you have the rows you want, click OK, and you get the Pivot you wanted in the first place:
Figure 1.29
Voilà! Now you see different measures starting in 2003.
You now get a single field in the field list:
Figure 1.30
Reusable! Portable!
Just like one of Power Pivot’s other great benefits, portable formulas (see
http://ppvt.pro/PORTABLEFX2 ), sets are portable, too! To see this in action, create a new Pivot:
Figure 1.31
In this new Pivot, merely selecting Set1 gives you an identical-rows layout.
Named Sets Don’t Work with “Traditional” Pivots
You may be wondering why you’ve never seen this feature of Excel before. There’s a simple answer: It works only with Power Pivot and data model Pivots (as well as another flavor of Pivot, the OLAP Pivot, but that’s much less common).
As you can see here, named sets do not work on “traditional” (non–Power Pivot) Pivots—which is yet another reason to use Power Pivot (or the 2013 data model):
Figure 1.32