Pareto Analysis the Easy Way

 
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.

10 Responses to Pareto Analysis the Easy Way

  1. Martin says:

    Rob, I am just now stocked on something similar. I have revenues in several currencies, by client and due status, and I want to display the amount of the revenue grouped by currency and due status, and the percentage it represents, but of the currency, since it has no sense to mix currencies (that is, the total in USD is the 100% in USD, divided in an x% due and y% not, the same in the rest of the currencies).

    Any comments?

    Rgds,

  2. Yes the power of combining DAX with Excel features is amazing. I was did a presentation to finance folks last night and someone asked me how do I do a top 2 for products in each productgroup. I created an awesome formula in DAX using rankx, then someone here told me today Excel supports this out of the box, guess we all have some learning to do :)

  3. D@ve says:

    I used running totals and runnning total percents in standard pivot tables for years. When I got to powerpivot, I somehow lost that knowledge. I tried many DAX solutions that were ugly and did not work. Then one day, the knowledge came back. But, would it work with DAX measures. I’ll admit I was a bit nervous. When it did work, I felt kind of guilty…..sorta like I cheated on DAX. So with your post Rob, I guess I can feel comfortable now :). Thanks!

  4. Vincent says:

    A good Pareto chart highlights the largest sales in the list by allowing you to quickly ignore the small sales. In your example you need to rank the products by sales in descending order then perform a cumulative running total. To make the analysis more meaningful, group all the products past the first 4 or 5 largest sales into other.

    Excel’s built-in “Show Values As” functions provide a good start but would not accomplish the above (as least not without manual intervention). I do agree with the premise of your post, don’t overlook Excel’s built-in functionality. I’ve yet to master the array functions and the solver add-in.

  5. John says:

    You have Given us a good teaser. Are you going to talk about pareto in a measure without using calculated columns that make slicers not able to cut the abc’s (Marco Russo’s approach to the probkem from 2010. I have thought that summarize might be the key here but I not to sure. Looking forward to how you solve the problem.

Leave a Comment or Question