Scorecard with Clickable “Key Performance Indicator” Metrics
(Yes, the colors are awful, sorry – I lack that touch)
Picking up from Tuesday’s post…
When you click that “Customer Behavior” metric in the image above, you are taken to this detail page:
The single “19%” Number in the Scorecard “Expands” to this Full Detail on Click!
(Also note the “Back to Scorecard” Link)
Hyperlinks Are a MUCH Better than Making the User Find the Right Sheet Tab
Yeah, we were a bit “light” on new material here on the blog last week. That happens when I have four straight days of presentations out of town (more on PASS BACON later).
I was reading Kasper’s new book on the plane (yes, I was home less than 24 hours, and am now back on the road), and its singular focus on building a full-featured set of dashboards inspired me.
It is clearly time for a mega-post on hyperlinks!
As pictured at the top of this post, hyperlinks are a great way to construct a “menu” sheet.
Not only is this a BIG convenience for the “consumers” of your workbooks, but it makes the whole thing “feel” much better too. More professional, more like an application and less like a spreadsheet.
Yes, hyperlinks can link to other locations in the workbook! It’s easy in the Insert Hyperlink dialog:
Un. Freaking. Believable.
(See bottom of post for video of this in action!)
First of all, THANKS for being patient with the promos for PowerPivotPro University – we do have to pay the bills here of course, AND I have poured six months of my best work into it, so thanks for giving it your consideration.
But yeah, it’s time to get back to some serious magic tricks with data.
I don’t play Pokemon – neither the video nor card game variety. But I recognize my kind of sickness (cough cough fantasy football) when I see it.
And people who know me, ALSO know my sickness. So they point things out to me:
And that led me to this Reddit thread: Pokemon & Power Pivot…
Pretty Nifty PivotTable
Same PivotTable, Diagrammed/Explained
I’ve been dying to write this post for awhile now. Let’s say you want your grand totals to appear at the top of your pivot, like this:
Grand Totals at the TOP of the Pivot? Yep, no problem.
Typically you’d want to do this when your pivot is really “tall” – lots of rows – and you don’t want to force people to scroll down in order to see the grand total.
Pivots do NOT let you display grand totals at the top of the pivot – only the bottom – so a frequent workaround is to write a formula in Excel itself that sums the whole column of the sheet. Very clumsy, and damn near impossible when you have two fields on rows like above.
The trick is simple – add a dummy table to your Power Pivot data model:
Two Weeks of Refreshes Later… the Report Still Thinks
Nov 15 is What Everyone Wants to See First!
This is a trick I’ve been using forever but never blogged about. Enough of you are now using Power Pivot for SharePoint (PP Server) that its time has come. And really, it’s relevant on the desktop too.
On the day 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, tomorrow comes. And all of your slicers still have that “old” date selected, even after you refresh everything. Ick. Who wants to go and update all of those slicers to point to the latest date?
I sure don’t. So, like me, you just let them 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.
Custom “On Hover” Tooltips on Each Cell in the Dashboard!
(The yellow dot and distortion around mouse pointer are GIF side effects and do NOT appear in Excel)
Got a great question the other day from Oscar, a student in PowerPivotPro School…
[OSCAR]: “Is it possible to have a tooltip in powerpivot which shows additional information based on the cells selected (or mouse roll over). the info to be displayed comes in from a table created with cube fuctions on the same data source. So the coordinates of the highlighted cells would be inputs for the cube formula and result displayed in a tool tip dynamically.”
My first thought was “no, not possible.” Then ten seconds later, a guerilla-style hack came to mind. And then, my reply:
[ROB]: “Oscar you are a very, VERY bad man. I am now obsessed with this problem. There goes my Sunday.”
The Flag Appears at Top Level (Accessories), Telling You There’s a Problem Further Down.
Expand Accessories and You See the Culprit is Bottles and Cages (Specifically Road Bottle Cage)
Quick post today, continuing the saga of Anakin Skywalker. An extension to the bubble up ranks technique.
Let’s say you have a measure. 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 0 (or No/False) if there isn’t. Or maybe it returns a “regular” number, but when it crosses a certain line, your business has decided that is Bad.
Get it? The measure is named “Sara Problem!!!!” To pronounce it properly you have to add a question mark – so technically, it’s named… “Sara Problem?”
Great pun! But I can’t take credit for it. Back when we lived in Seattle, my wife (girlfriend at the time, and fellow Microsoft engineer) played roller derby. She was a “Rat City Rollergirl,” she played for a team called Grave Danger, and her skater name was Natalie Fatality.
I am not making this up. Well, she had a teammate whose skater name was Sara Problem.
“Sara Problem” on Left, “Natalie Fatality” (Official Wife of PowerPivotPro) on Right
Try Doing THIS in Normal Excel: A Chart That is Indexed to Always
Start at 100%, Regardless of Time Frame Selected
(Yes it’s an ugly chart, but a beautiful technique)
I love this kind of thing. Last week I posted about measures that are indexed/normalized to make the scales match. The two techniques I suggested were “divide by max value” and “divide by average value.”
Jon Peltier, Excel MVP and Visualization Guy, dropped in on the comment stream with some of his frequently-used techniques, such as “divide by first value.”
I was flattered to see Jon show up here – I’m a Formula Guy, he’s a Chart Guy, and we’re not supposed to fraternize (kidding about that part). In all seriousness, our paths do not cross very often. We struggle sometimes to find common ground. But now, we have discovered our point of overlap. (Cue ominous music, “the world may never be the same again,” etc.)
Can YOU Spot the Green or the Red Line? (Bonus Q: Can You Tell
That the Green Line is 50x higher than the Red Line?)
After failing to maintain my Tuesday & Thursday posting schedule over the past two weeks (one post total, out of four scheduled), I’m BACK folks. I mean this week didn’t stand a chance. BAM! Nailed it.
OK, enough self-congratulation for meeting the minimum standards I set for myself. Moving on!
The three lines plotted on the chart above are “sourced” from these numbers:
These Three Measures Are VERY Different in their Relative “Sizes,”
Varying From 2 Digits to 6 Digits.
Data like that results in crappy charts. Let’s fix it with some formula magic:
Ah, Formulas Make Everything Better. Yep, it’s the same data, just “normalized.”
Yeah, I hear that chittering out there – the mandibles of the Demonspawn Chart Fiends are clacking out a sound that resembles “Secondary Axis!”