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

The base data looks like the below:

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

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.

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

George,

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. 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. March 1, 2015 at 4:25 am

dear george
many thks for that great tip
all the best

4. 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. June 18, 2015 at 3:45 am

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

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

• 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?