“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:
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:
Product on Rows, Sales Measure on Values – Nothing Fancy
Then Right Click One of the Sales Cells, Sort Largest to Smallest
So now you have a sorted pivot:
But now for the magic trick. Right click again, choose Show Values As, and Running Total In:
That pops a dialog. Choose the field that is on Rows – Product Name in this case:
Now you get this:
This is Now a Running Total! No DAX!
Go back to the Show Values As flyout and this time choose % Running Total In:
Switch from Running Total to % Running Total
And then we get:
Here’s Where We See Just These First 13 Products Account for 50% of All Sales
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
For other folks who’ve been up to their eyeballs in DAX for awhile, take a moment and review the options on this sucker:
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:
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.