Home > BI, BISM Tabular, DAX, Excel, PowerPivot > Displaying Top N, Bottom N, and “All Others” – Dynamically

Displaying Top N, Bottom N, and “All Others” – Dynamically

I just read Rob Collie’s post Displaying Top N, Bottom N, and “All Others”. Its a great post with lots of details.

I’ve come up with another approach that use the Set to achieve the similar outcome but dynamically. The final solution looks like the below

Dynamic TopN and AllOthers

The base data looks like the below:

base data

Both table as added to Model as the linked table.

I created the following calculated measure in the Power Pivot Model:

SelectedN:=MAX([N])

Total Sales:=SUM(Sales[Sales])

Rank:=RANKX(All(Sales),[Total Sales])

Top N Total Sales:=CALCULATE([Total Sales],FILTER(Sales,[Rank]<=[SelectedN]))

All Other Total Sales:=CALCULATE([Total Sales],FILTER(Sales,[Rank]>[SelectedN]))

In addition to the DAX fomula, I created two sets using MDX

Excel OLAP Set

Set Name: TopN

Set Definition: TOPCOUNT([Sales].[CustomerID].Children, [Measures].[SelectedN], [Measures].[Sum of Sales])

Set Name: AllOthers

Set Definition: [Sales].[CustomerID].Children – [TopN]

* Both of the above two sets has the checkbox “Recalculate set with every update” checked.

You can download the workbook (Excel 2013 version) at: https://onedrive.live.com/?cid=8BA997FE18F7FDAE&id=8BA997FE18F7FDAE%212373

Advertisements
Categories: BI, BISM Tabular, DAX, Excel, PowerPivot Tags: ,
  1. Chris Gilbert
    November 13, 2014 at 12:15 am

    George,
    A great post! I really like the simplicity of your example (and downloadable workbook).

    Thanks. I’m going to be able to use this technique a lot! We do a lot of inventory analysis, and TopN/BottomN is a frequently requested feature.

    • November 13, 2014 at 8:31 am

      Thanks Chris. I’m glad you like my example.

  2. Patrick
    December 4, 2014 at 3:18 am

    Great post George! I would like to try this on one of my data sets but I am not able to download the file from the supplied link for review. Once I click on the link OneDrive just clocks and does not pull anything up. Any thoughts?

    Thanks,
    Patrick

    • December 4, 2014 at 8:09 am

      Thanks, Patrick. Make sure the firewall is not blocking the OneDrive. Ill double check the link when I get to office.

  3. hermine
    March 1, 2015 at 4:25 am

    dear george
    many thks for that great tip
    would be great if i could download workbook in excel 2010
    all the best

  4. Alvin Tan
    June 15, 2015 at 4:19 am

    Hi George,
    Thanks for the demo.
    I am facing an issue. If I have duplicate IDs (ie, returning customer performing another sale), I am unable to get the aggregated sales from the returning customer.

    Instead the above, it will rank the highest sales from the same customer instead ranking the AGGREGATED sales from the same customer.

    Wondering do you have any pointers for this.

    • June 15, 2015 at 11:44 am

      I would suggest you perform an ETL to make sure the table contains proper data to perform the aggregation. Cleanse the data in ETL is much much easier in Power Pivot data model.

  5. June 15, 2015 at 4:20 am

    Hi George,
    Thanks for the demo.
    I am facing an issue. If I have duplicate IDs (ie, returning customer performing another sale), I am unable to get the aggregated sales from the returning customer.

    Instead the above, it will rank the highest sales from the same customer instead ranking the AGGREGATED sales from the same customer.

    Wondering do you have any pointers for this.

  6. John
    June 18, 2015 at 3:45 am

    @Alvin – Did you figure out a solution? I’m having the same problems as you.

  7. Chris Hunter
    February 3, 2017 at 8:35 pm

    Hi George,

    I’m not the greatest with Excel but I’ve been asked to create a report that needs data represented in this exact way.

    Do you use standard pivot tables to achieve this or do you use an add-in?

    At the moment I can’t create sets and I’m wondering if it’s due to not having a particular add-in activated?

    Thanks in advance.

    • Chris Hunter
      February 3, 2017 at 9:10 pm

      Apologies! After digging it looks like Power Pivot is only available in 2013 standalone and professional+ but I’m currently running standard.

  8. April 24, 2017 at 10:27 pm

    I cannot make it work as my data is slightly more complex. I have customerids repeating as i am trying to capture how their balance evolves in time. so for a 3 month daily frequency and let’s assume 200 clients i have 80 daily balances (row) per customer * 200 customers

    when i try to replicate the rank and top n total sales i get an error message in the measures.

    any ideas how to fix this?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: