Post by Rob Collie

image

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:

image

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:

image

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:

image

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

image

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

image

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 Smile

Next, a Disconnected Table!

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

image

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

image

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

image

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:

image

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

image

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:

image

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!

image

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! Smile

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 17 Comments

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

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

  7. 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. Yeah I’m having the same problem as Barbaros. If there is a workbook out the there for this that would be great

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

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

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

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

Leave a Comment or Question