Creative Visualizations

Compare product performance after launch

By Avichal Singh

As part of the microsoft.com/learning team we release products throughout the year –courseware, books, exams and eLearning (check out MVA for tons of free courses). We often need to understand how our products perform after launch and how they compare against each other (in the first month since launch, first quarter, first year etc.). For Example: we would compare the various courses we launched around Visual Studio 2010. We may compare Visual Studio 2010 against Visual Studio 2012 courses. We may even compare Visual Studio against SQL Server. Or compare adoption by geography or customer segments.

I can imagine similar need for other businesses, e.g. a car manufacturer who needs to compare performance of various year, make and models.

Power Pivot and Power View can allow us to go from View 1 below, which is inscrutable at best, to View 2 which really helps us understand and differentiate the adoption ramp of various products. In this article, I would explain how you can go from View 1 to View 2 using the car manufacturer example.

View 1: Monthly Sales by Car Model
Typical view available in BI, but not very insightful

Power View Graph Monthly Sales by Car Model

 

View 2: Cumulative Sales since Launch, by Car Model
Clear view into adoption ramp of various products
Power View Graph Cumulative Sales since launch by Car Model
Read the Rest

Cross-Sheet Hyperlinks Part Two

 
Power Pivot Scorecard Mockup With Clickable KPI Metrics

Scorecard with Clickable “Key Performance Indicator” Metrics
(Yes, the colors are awful, sorry – I lack that touch)

Not Just for Sheet Navigation, But Also for “Drilldown”

Picking up from Tuesday’s post…

When you click that “Customer Behavior” metric in the image above, you are taken to this detail page:

Detail Drilldown Report - Just a Pivot Designed by Me On Another Sheet

The single “19%” Number in the Scorecard “Expands” to this Full Detail on Click!
(Also note the “Back to Scorecard” Link)

That Was a Cube Formula Cell!

Read the Rest

Pro Tips: Cross-Sheet Hyperlinking Part 1

                            image

Hyperlinks Are a MUCH Better than Making the User Find the Right Sheet Tab         

Sorry About Last Week Smile

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).

Inspired by Kasper’s Book!

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!

First Usage:  a Menu Sheet

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:

Read the Rest

Optimal Set Selection, Power Pivot Plays Pokemon, and My Brain Just Exploded

image

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:

And that led me to this Reddit thread:  Pokemon & Power Pivot…

Read the Rest

Grand Total Mania! (Totals at Top & Multiple Totals)

Let’s start 2014 with a bang…

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.

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:

Read the Rest

A Simple Trick for Combating “Stale” Slicers

 
Help! My Power Pivot Slicers Don't Auto Update to the Latest Date on Refresh!

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.

Read the Rest

Custom Toooltips in Dashboards!

 
Power Pivot Dashboard Tooltips/Comments

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!

Read the Rest

Bubbling Up Exceptions with “Sara Problem?”

 
Power Pivot Allows Some Amazing Stuff, Like Exception Flags that "Bubble Up!"

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!

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.

This has nothing to do with Power Pivot, or at least not directly. But c'mon, it's badass :)

“Sara Problem” on Left, “Natalie Fatality” (Official Wife of PowerPivotPro) on Right

Back to the Formulas!

 

Read the Rest

Charts that are dynamically “indexed” to first value

 
Auto-Index Chart: Another awesome new Power Pivot technique

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…

"When you little excel mvp's get together you're worse than a sewing circle."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!

 

Read the Rest