Do YOU Want To Be A Report Superstar? Hello again P3 nation, today I’d like to drop some reporting knowledge. I’m going to share some of my best practices for Power BI Reporting I’ve developed over the years. As many…
by Matt Allington Today I am sharing a trick that I have used a number of times for clients – it allows you to use simple VBA to jump from one pivot table to another, and when you arrive at…
OK, you’ve been a good Power Pivot author and given your measures clearly descriptive names.
Your punishment is spending all day looking at pivots like this:
Hey, Where’s the Rest of My Information?
(Hint: It’s in “Scrollsville.”)
MUCH Better: Last Two Measures Completely Visible, With Space to Spare!
(Assuming Vertical Space Isn’t a Problem, Of Course)
A Trick I “Harvested” From a Client
Awhile back I was working with a gentleman named Tom Phelan who repeatedly used a series of click mouse clicks to achieve the sort of layout pictured above. After seeing him do that about ten times I asked him to slow down so I could see what the clicks were.
That’s a Single Spreadsheet Cell with a CUBEVALUE Formula AND a Sparkline in It!
I was working with a client last week when a question occurred to me:
“Can I put a Sparkline in a cell that already has a Cube Formula in it?”
”Oh cool, it worked!” (Cackles Maniacally)
-me, last week
Anyway, we were off and running at that point:
“FrankenSpark” Used as Part of a Larger (Redacted and Obfuscated) Client Scorecard
(Yes, the Colors Still Need Some Work)
Even Very “Sophisticated” Reports/Dashboards are Often “Couch Potatoes” in Practice
(They Sit There and Expect You to Do All the Work)
Intro From Rob
I’ve been meaning to blog this for a long time – it is, after all, one of my absolute FAVORITE things to talk about. Now, Scott beats me to it. But he does such a good job below that I don’t have much to add, except a few graphics here and there, like the one above.
Besides, how can I not love a blog post that starts off talking about how right I am?
Guest Post by Scott at Tiny Lizard
By nature, I am a rather skeptical person. When I first hear an idea, I generally think it is probably wrong. This isn’t one of my finer traits and I am sure it drives Rob insane, since he has a rather impressive track record of being correct. Thankfully, this blog entry is not about one of the times I assumed Rob was wrong…
It is actually the complete opposite. It is one of those times when Rob told me something and it just instantly SUPER resonated with me and got stuck in me. I suspect it will do the same for most of you.
Nouns and Verbs
If you were fortunate enough to hear Rob speak recently at PASS Business Analytics Conference, you have already heard this idea.
It is super typical for us, as report authors, to generate The Report. The Noun. We hand it off with pride. And The Report has all the information somebody could possibly need to make a decision. All of it. Row after Row. Column after Column. Unfortunately, even with the fanciest conditional formatting in the world, it is not clear… how is somebody supposed to look at The Report and actually… do something?
Because, at the end of the day (and yes, with a handful of exceptions like keeping the SEC happy), it is our hope that somebody looks at our reports and uses it to make a decision. To do something. To Verb.
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)
Question from PowerPivotPro School!
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 Trick: Hyperlinks to Nowhere!
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)
Start With a “Sara Problem” Measure!
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
Back to the Formulas!
Has THIS ever happened to you? We’re not even out of the Aarons yet.
Simple and Slick
Yesterday I saw Ken Puls post a really simple and effective trick – intentionally introducing a BLANK() measure in your pivot just to add a spacer column or row – and my response was “simple and slick, I like it.”
Well today I’m in Michigan, conducting some PowerPivot training/consulting, and that makes today a great day for a similarly slick and simple trick. Because hey, I’m not even here right now. Who’s writing this post??
Over in the Customers table in the PowerPivot window, add a simple calc column:
Each Number Refedit in the Edit Rule Dialog Points to a Different Cell on the Sheet
Correcting for that bug
In the last post on this topic, I discovered what I think is a bug in Excel 2010 conditional formatting, one that prevented me from using the Percentile threshold type with a cell reference.
But the Number type works great, so if I can get the actual [Profit] value for, say, the 80th percentile Model Name into a cell, I can reference that.
And that’s precisely what those three cells in the image above contain:
Cell F2 Contains a Cube Formula that Returns the Measure [Profit Required for Green CF]
OK, so how do I calculate that [Profit Required for Green CF] measure?
Note the “Readout” that Displays the User’s Slicer Selections:
Now Do This for Every Pivot in Your Workbook With One Click!
A Common Trick, Now Automated
This is something we do all the time at Pivotstream – we write formulas that capture user slicer selections and then display those selections back to them.
We do this via hidden report filters:
(Yes, we could also do this with cube formulas, but we started out (literally years ago now) using this approach and we’ve just kinda stayed with it. I’m not sure cube formulas would be better, but they might be.)
Why is the Readout Useful?
Why do we do this? Well, for one thing, the Download Snapshot feature on the server does NOT download the slicers – you get a big blank white space where the slicers were, which isn’t terribly helpful. It leaves you wondering what you had selected.
It’s also useful when there are slicers on other sheets impacting your current sheet. And even on a single sheet, it’s often nice to have a compact readout of your selections without having to scroll (or even scan with your eyes) to see what selections you have made.