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

X

## Subscribe

#### Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

## This Post Has 13 Comments

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

Rgds,

1. powerpivotpro says:

Have you tried the various “percent of total” options in the Show As flyout yet?

1. Martin says:

yep. none of them appeared to be the one I need. i’m using 2010…

2. Ted Murphy says:

Hi Rob,
Did you ever get to show the DAX Solution to the Pareto Problem as intimated on May 16th (my birthday!!).

Thanks,

Ted.

1. powerpivotpro says:

No, I never did. Many other things intruded. I’ll add this back to my list 🙂

1. Richard says:

Is this practical on a large table of 100mil rows plus?

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

6. Man… good stuff… you saved my life… I was having to do that manually all the time… thanks man !!!

7. Meg says:

I was trying to work out how to do this with DAX and never thought of this trick either. Brilliant thank you!