Un. Freaking. Believable.
(See bottom of post for video of this in action!)
Back to Our “Regular” Programming Today
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.
A Funny Thing Happened On Reddit and Twitter…
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:
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.
How DO You Do It, Then?
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!
Your Dashboard is Refreshed, But its Slicers are Stuck in “Yesteryear”
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.
[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.”
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)
One Idea Leads to Another, and Another…
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.)
Portable Formulas Are a MAJOR Help to Chart People!
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?)
Look Ma! Two Posts!
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!
Three Different Scales
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.”
A Word from the Charting Pit of Derision!
Yeah, I hear that chittering out there – the mandibles of the Demonspawn Chart Fiends are clacking out a sound that resembles “Secondary Axis!”