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

February 13, 2014

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 of this entry »


Another Way to Find the Top-Performing “Thing”

January 14, 2014

 
Best Selling Product, or Date, or Top Performing Player - All Can Be Handled, Including Ties, in Power Pivot!

Pretty Nifty PivotTable

Best Selling Product, or Date, or Top Performing Player - All Can Be Handled, Including Ties, in Power Pivot!

Same PivotTable, Diagrammed/Explained

Revisiting an Older Topic

Read the rest of this entry »


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

January 7, 2014

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 of this entry »


A Simple Trick for Combating “Stale” Slicers

December 3, 2013

 
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 of this entry »


Custom Toooltips in Dashboards!

November 12, 2013

 
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 of this entry »


Bubbling Up Exceptions with “Sara Problem?”

October 17, 2013

 
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 of this entry »


Charts that are dynamically “indexed” to first value

October 8, 2013

 
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 of this entry »


“Normalize” Your Measures for Fun and Profit!

September 26, 2013

 
image

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:

image

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:

Converting Values to Equivalent Scales for Charting - another Power Pivot Magic Trick!

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!”

Read the rest of this entry »


Ranks and Exceptions that “Bubble Up” to Subtotals

September 17, 2013

 
image

One Training Session, Two (Three?) Blog Posts Inspired

Every now and then, I find myself training someone who I just KNOW is going to be teaching ME things in the near future.  The questions they ask are the dead giveaways.  And the next time we meet, they will be Darth Vader, telling my Ben Kenobi about how the circuit is complete.

I had the pleasure of teaching someone like this quite recently.  He forced me to write some pretty nifty calcs, or at least some that *I* think are nifty.

The Formula

Read the rest of this entry »


Moving Averages Controlled by Slicer

August 12, 2013

 
Two Month Moving Average in Power Pivot Six Month Moving Average in Power Pivot

Two Month Moving Average is Pretty Smooth.  But Six Months is Smoooooother.
(Imagine Barry White Saying That:  “Ohhh Yeaahhh…  A Six-Month Moooving Average.  Smoooooth…”)

It’s already been a couple weeks back (yikes!), but I recently wrote a post on simple moving averages in Power Pivot.

One of the questions, in the comments, was how to control the “length” of the moving average dynamically:

image

What’s That???  Oh No!  That’s DISCONNECTED SLICER’s Music!!
(Yes That’s a Reference to Pro Wrestling Entrance Music, A Fascinating Read)

Hey, when TWO people ask for something, and one of them deploys CAPITAL LETTERS in the effort, and then pairs said uppercase with one of my favorite words “(“enhance”), well, I’m hooked.

Read the rest of this entry »


Simplifying Time Calculations and the User Experience using Disconnected Slicers

June 25, 2013

Guest Post by Jeff Lingen [LinkedIn]

How does PowerPivot fit in an enterprise BI environment

We don’t even know what it is yet. We don’t know what it is. We don’t know what it can be, we don’t know what it will be, we know that it is cool.

Zuckerberg’s early assessment of Facebook was a lot like how I felt after first discovering PowerPivot 3+ years ago. I knew it was cool but had no idea how it would fit into an enterprise business intelligence environment. For a long time PowerPivot for me was just a cool thing that I used for my own data analysis or for proto-typing tools that I would eventually turn into “enterprise-level” solutions. Today I need a pretty compelling reason not to use PowerPivot for almost all of my organization’s analytic requirements. So where does PowerPivot fit into the enterprise BI environment and how do you get associates engaged and use it to provide value?

Read the rest of this entry »

PowerPivot vs. Power View: What’s the Difference?

June 18, 2013

 
I’ve been getting this question a lot lately:  How does Power View relate to PowerPivot?  Is PV a replacement for PP?   (And why does PV have a space in it while PP does not?)

First:  Understand that PowerPivot is Kinda Two Things

Let’s rewind all the way back to Office 2010, a world in which PV does not exist.  (For most of you, we call this time Today.  And for those stuck on 2007 or 2003, you may refer to this as Tomorrow.  Or maybe even the Day After Tomorrow).

In that world, which is where this blog largely lives, it’s helpful to reflect that PowerPivot has two parts:  the PowerPivot window, and the Excel window.  They have the following relationship:

PowerPivot's Relationship to Excel

 

Read the rest of this entry »