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