image

“Computer?  Tell me how many products account for the top half of all sales?”

The scene in Star Trek IV where Scotty picks up the mouse and tries to instruct the computer by speaking into it was, in my opinion, the lone bright spot in an otherwise lackluster movie.  We all feel the same way sometimes of course.  We just want to say “hey computer can you just do what I want without making me trick you into doing it?” 

(Related:  I had a professor once who liked to say “as computer scientists we are trained to communicate with the dumbest things in the world – computers – so you’d think we’d be able to communicate quite well with people.”  He also writes a science and politics blog and was one of my favorite professors.)

OK, so how do we build something like this in PowerPivot:

image

The Handful of Products Shown Account for 50% of All Sales – But How Did I Build This?

First, Don’t Make it Harder Than It Is!

A large corporation recently hired me to introduce PowerPivot into their reporting and analysis teams.  And on day two, they asked me how to do what is sometimes called Pareto Analysis – if I rank products (or customers) from 1 to N in terms of their value, how far down that list do I need to go before I hit 50% of all sales, or 80% of all sales, etc.?

Well my first instinct was to do something fancy in DAX.  Something “clever,” as Tyler Durden would call it.  And I think I’ll share that approach on Tuesday.  So I launched into a DAX approach on the whiteboard.

But then someone asked “hey can’t we just use Show Values As?”  I paused and scratched my head.

Um, yes.  Show Values As – an Underrated Tool

Check this out.  DAX-free solution.  You don’t even need PowerPivot for this – it works in a regular pivot too.

Just add Product and Sales to your pivot like you always would:

image

Product on Rows, Sales Measure on Values – Nothing Fancy

image

Then Right Click One of the Sales Cells, Sort Largest to Smallest

So now you have a sorted pivot:

image

But now for the magic trick.  Right click again, choose Show Values As, and Running Total In:

image

That pops a dialog.  Choose the field that is on Rows – Product Name in this case:

image

Now you get this:

image

This is Now a Running Total!  No DAX!

Go back to the Show Values As flyout and this time choose % Running Total In:

image

Switch from Running Total to % Running Total

And then we get:

image

Here’s Where We See Just These First 13 Products Account for 50% of All Sales

Tradeoffs

OK, longtime Excel pivotheads are yawning right now.  “Come one Rob we’ve known about this forever.”  I have a confession to make:  I’ve been aware of it too, but I hadn’t really paid too much attention to all of the options on that flyout.  Percent of total was the one option I’d used a lot of and the others just seemed to disappear into the background for me.

So thank you Lisa for bringing this back into focus for me Smile

For other folks who’ve been up to their eyeballs in DAX for awhile, take a moment and review the options on this sucker:

image

So Many Options!  Take a Tour, Try Them Out.

But there’s a catch.  PowerPivot has no idea that the numbers have been changed to percent running totals.

For instance, let’s write a new measure:

[My New Measure]=
[Total Sales] * 2

and see what we get:

image

My New Measure Returns 2x the Original Dollar Sales Value, Not 2x the % Running Total Value

So if you want to use your fancy % Running Total value in another measure calculation, this Show Values As technique is no help.

This is because even in the % running total column, PowerPivot sent only the original sales numbers to Excel.  At the last minute, before displaying those numbers, Excel itself runs some calculations of its own (think of it as “post-processing” of the numbers given to it by PowerPivot) and then displays those results.  The PowerPivot calculation engine never sees those % running total numbers, in other words.

On Tuesday I plan to show a DAX solution to this problem for contrast.