Post by Rob Collie

If We Use Excel’s Built-In Top N Filter to See Our Top 1,000 Customers, It Hides the Other Customers Completely.  But Using DAX, We Can Just “Split” the Audience into Two Groups.

### This Came Up Recently…

Hey, I absolutely ADORE the TOP N filter capability offered by all Excel Pivots.  It kicks major booty and we use it all the time:

The Top 10 / Top N Value Filter in Pivots:  Get to Know It, It Does Amazing Things

But If we set that to show us the top 10 customers, it shows us JUST those 10 customers:

OK cool, we see those top ten customers, and that they collectively purchased \$132,026 of stuff from us.

But we want to ALSO see how much the OTHER (non top 10) customers are worth too.

No problem, we can then change the pivot option setting to Include Filtered Items in Totals:

Changing This Checkbox from Off (Default) to On Yields the Pivot Below…

OK, Now We See the Total for ALL Customers.  But We Have to Do the Math Ourselves,
AND We Can’t See Those Other Individual Customers

So, we’re still left a bit unsatisfied.

### A Solution via Power Pivot, DAX, and Disconnected Tables

I’m onsite all week with a customer this week so I am going to race through this a bit, without quite as much explanation as usual, and then we can chat in the comments.

First, let’s create some Measures (aka Calculated Fields):

Three Measures Created.  [Total Sales] is simple.  The Other Formulas Are Below.

[Top 1000 Customer Sales] :=

CALCULATE([Total Sales],
FILTER(Customers,
RANKX(ALL(Customers), [Total Sales])<=1000
)
)

[Sales to Customers Outside the top 1000] :=

CALCULATE([Total Sales],
FILTER(Customers,
RANKX(ALL(Customers), [Total Sales])>1000
)
)

Note that these two differ only by the highlighted comparison operator.

### Why FILTER instead of TOPN?

I *do* have a reason, but no time to explain.  Just trust me for now that it was needed

### Next, a Disconnected Table!

Now, create this table and link it over into Power Pivot:

Manually Create a Table Like This in Excel, and then…

…Link it Over Into Power Pivot, as Shown Here.

Then, WITHOUT Creating Any Relationships, Put That Group Field on Rows of Your Pivot

### Now for a “Wrapper” Measure!

Create the following measure:

[Wrapper Measure for Top 1000 Customer Sales] :=

IF(ISFILTERED(‘Top'[Group]),
SWITCH(MAX(‘Top'[ID]),
1, [Top 1000 Customer Sales],
2, [Sales to Customers Outside the top 1000]           ),
[Total Sales]    )

Put THAT measure on the pivot:

And now you can put Customer ID on the pivot too if you want:

Boom!

### Note – there are 1,013 Customers in the Top 1,000

Because of ties, there are actually 13 more customers in the top 1000 than we’d expect:

### Repeat for Other Metrics!

Let’s say we also have another “base” measure like this:

[Sales per Customer] :=

DIVIDE([Total Sales],COUNTROWS(Customers))

If we then repeat the entire pattern – the “top 1000” measure, the “outside the top 1000” measure, and then the “wrapper” measure, but substitute [Sales per Customer] in the right places, we get these (apologize for the formatting – time pressure).

CALCULATE([Sales per Customer],FILTER(Customers, RANKX(ALL(Customers), [Total Sales])<=1000))

CALCULATE([Sales per Customer],FILTER(Customers, RANKX(ALL(Customers), [Total Sales])>1000))

IF(ISFILTERED(‘Top'[Group]),
SWITCH(MAX(‘Top'[ID]),
1, [Top 1000 Customer Sales per Customer],
2, [Sales per Customer Outside the top 1000]                      ),
[Sales per Customer])

And the following pivot!

Note that We Could NOT Get the Highlighted Value By Subtracting \$7,040 from the Grand Total – So The “Included Filtered Items in Totals” Checkbox Would NOT Have Done the Trick.

Questions?  Fire Away!

#### 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 24 Comments

1. Nate says:

I am the thick of budgeting and one report I built lists top 10 customers by region. It would be great to work this into the report, but it sounds like I would have to do this for every column listed e.g. Sales, units, GM\$, GM% etc..

Not sure if the pain is worth the gain.

2. Wouldn’t it make more sense to put the logic into a base measure [Customer Count] so that the more advanced measure could be expressed more simply as
[Sales per Customer]: = DIVIDE( [Wrapper Measure for Top 1000 Customer Sales], [Customer Count] )
where [Customer Count]:
= IF(ISFILTERED(‘Top’[Group]),
SWITCH(MAX(‘Top’[ID]),
1, COUNTROWS(FILTER(Customers, RANKX(ALL(Customers), [Total Sales])1000))
),
COUNTROWS(Customers)
)

3. General Ledger says:

Rob,

Great stuff. This points out another good reason for all PivotTable lovers to spend some time with PowerPivot and see what they are missing.

4. General Ledger says:

Rob,

Can you stretch the magic a little more.

1. Can the number of top customers be selectable in a slicer by the user?
Just a few options like: Top 10, Top 100, Top 500, Top 1000

2. This might be insanity. Can the label All Other Customers automatically reflect the number of customers in the group? Such as All Other 1,234 Customers.

1. You have have a look at my post below. You can achieve it but not using pure DAX. Instead, you have to use the OLAP Sets in Excel combined with DAX.

1. Hi Rob, thanks for pointing out you can use pure DAX to achieve TopN but I’m not so sure you can achieve the TopN as well as “All Others” at the same time, as this post originally discussed.

5. Jeff Wilson says:

Can we have the example sheet

6. Patrick says:

Hi Rob, Great post and thanks for all your efforts!!

I split my data into three groups (top 25 / 26-50 / other) with success. I want to add an additional dimension under ‘Customer Name’ such as Product. When I do this some customers get split between groups with certain products being represented in each group (nothing is duplicated just split). Do you have any ideas as to what may cause this issue? Is it possibly due to the lack of a relationship between the ‘Group’ table and my ‘Master Data’ table?

7. Patrick says:

Great book Rob! It has really helped me ramp up my skill set in a short amount of time.

I would like to reach out to the powerful DAX minds here to help me out.
I have a Power Pivot file that has a Master Data table (payer name, revenue, cost etc.). I have a customer table that has the original payer name and some subsequent groupings such as region, key accounts etc. (Many to one relationships)

I have been able to create a top 25/next 25/other breakdown of my data using the example above. The issue I have is that the filtering is always looking back to the original payer name. For example my top 25 for regions only contains 14 regions that consist of 25 payers. (I hope that makes sense)

I would like my rankings to be based off of the subsequent grouping so that my top 25 would consist of 25 regions that contain say 47 customers. Said another way… How do I get by groupings to be based on subsequent classifications instead of the original raw data?

Any help you can provide will be greatly appreciated…. I have been trying for days to accomplish this.

8. Barbaros Sağlamtimur says:

Is there a sample workbook that I can play on it? Somehow I couldn’t manage this work. I did it for Top 10 and
Top 10 Customer Sales:=CALCULATE([Total Sales];
FILTER(Table1;
RANKX(ALL(Table1); [Total Sales])<=10 )
)
returns incorrect result. So do [Sales to Customers Outside the top 10] and [Wrapper Measure for Top 10 Customer Sales]. Any clues?

1. John says:

Yeah I’m having the same problem as Barbaros. If there is a workbook out the there for this that would be great

9. Rob- This is a great post that I have used successfully before where I filtered on a dimension as you do in the example above. I am challenged today with a table factInvoices and dimDate where I want to group the Top 25 Invoices and All Other Invoices. If I use your DAX above and instead of filtering the CustomerDim as in your example I filter factInvoice – it works as long as there are no active dimDate related slicers. I suspect there is a way to modify the All(Invoices) part of the DAX such that it won’t negate my dimDate slicers… but I can’t come up with it… Suggestions???

This works, but not when sliced:
Revenue:=sum([RevenueColumn])
Top25:=CALCULATE([Revenue], FILTER(factInvoices, RANKX(All(factInvoices), [Revenue])<=25))

10. Chris R says:

I have two (separate) questions/requests on this:

1. How can I turn this into three groupings, to show the top 10, bottom 10, and others. The purpose of this is for variance analysis, where I want to see the largest variances (based on absolute value, so top 10 positives and bottom 10 negatives, and others).

2. Can this be done so that it isn’t a top N by count, but by percent of total? So, if I want to show the top 80% grouped and then others?

11. Sanjay Shah says:

Hi Rob :
I tried the above and it worked. I tried it for top 10. However i need to take this a bit further. I need create a calculated column in the customer table which would show a customer category. This category would show the Customer Name if the customers rank is in top 10, otherwise it should club all other customers into one category say “Others”. That way i wont have too much of clutter when i see the customers in a area map in PowerBI. the user can always drill down from the “Others” category to see which customers constitute others if required, Could you help in this area please ?

12. Hunter says:

When a table has many columns in it and you only want to pull the top amount from a specific column how do you do that? When i try to select a certain column in my formula after the RANKX(All( it pulls back all of the data and does not bring back the top amount requested. Other than creating new tables with only one column in them how can I get the formula to return the right amount from the desired column? Thank you!

13. Trevor says:

But the title of the post also refers to “BOTTOMN”? How do I return the name (or other value) of the lowest rated thing (ie. by sales)?

14. Rob and co – thanks a lot, you can’t get more clearer in your explainations, you just make it all happen in a few sentences.

15. Syed Abdul Nayeem says:

Hi Iam looking for help on top20% of rows example mentioned below.

SKU Season Option Count Option Sold value Sold units Comments Top 20% Calculations Total Options from Winter
1 Winter 0.5 A 500 1 Top20% of best Sellers = 9 products *20% percent 2
2 Winter 0.5 A 200 2 Top20% of best Sellers
3 Winter 1 c 200 2 Top20% of best Sellers
4 Winter 0.5 d 200 2
5 Winter 0.5 d 200 2
6 Winter 1 f 200 2
7 Winter 1 g 200 2
8 Winter 1 g 200 2
9 Winter 1 h 200 2
Total Options from Summer
1 Summer 1 a 300 3 Top20% of best Sellers = 21 products x 20% 4
2 Summer 1 b 300 1 Top20% of best Sellers
3 Summer 1 c 90 2 Top20% of best Sellers
4 Summer 1 d 60 3 Top20% of best Sellers
5 Summer 1 e 30 3
6 Summer 1 f 30 3
7 Summer 0.5 g 30 3
8 Summer 0.5 g 30 3
9 Summer 1 h 30 3
10 Summer 1 i 30 3
11 Summer 1 j 30 3
12 Summer 1 k 30 3
13 Summer 1 l 30 3
14 Summer 1 m 30 3
15 Summer 1 n 30 3
16 Summer 1 o 30 3
17 Summer 1 p 30 3
18 Summer 1 q 30 3
19 Summer 1 r 30 3
20 Summer 1 s 30 3
21 Summer 1 t 30 3

Pleas help me on this query on power bi with formula and funtion to be used.

16. JW says:

I got an error message – The value column ‘Customer’ in the table ‘customers’ cannot be determined in the current context…..please help! Thanks

17. Enayat says:

i can get the invoice count following your pattern, however struggling to the correct invoice percent over the top 1000 & others by customer level. this is gives me the correct invoice counts both @ the topx level and the customer level. just not sure how to get the correct percent over the customer level.

Top X Invoice Count:=IF(ISFILTERED(dTopX[Group]),SWITCH(MAX(dTopX[ID]),
1, [Top 10 Cust Invoice Count],
2,[All Other Cust Invoice Count]),
[Number of Invoices])
.
this measure gives me the invoice percent over the TopX group & all others
Top X Sales_Invoice Pct:=DIVIDE([Top X Invoice Count],[All Invoices])
however @ the customer level it says 100%

18. Hi Rob, great Post, thank!

I would like to ask you a question. We are building a Power BI report and we want to represent in a stacked bar chart the top N of a category (or dimension) plus the others. With your approach I am getting in one level the sum of the topN and the sum of the others but I need the numbers in the same level. I explained my challenge with images in the MSDN forum question: https://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/5cd59264-efc1-4c3e-995f-5cf3af33b834/

Any comments will be highly appreciated.

Kind Regards, Paul.

19. Tony says:

Hi Rob, could you explain why you used RANKX rather than TOPN?

Thx, Tony