This will become the first post in a series using DAX to create / calculate Groupings and Binnings.
A very common requirement (at least one that I've encountered very often in the recent past) is to show the Top / Bottom N of something. This task can easily be accomplished either by using the Top N setting from within the Visual level filters pane or using the DAX function TOPN.
But as soon as the user also wants to include the remainder, a number that represents the rest, meaning the sum of all the remaining members, this request seems to be overly complex. What the user asks for may look like this:
For simplicity I decided to show the top 5 months, here the remainder "Other" is composed by the remaining 7 months. This table shows the underlying data:
Because this is the first post about groupings I want to state some facts that have to be considered to compose a solution for the above mentioned requirement
- only natural columns (originated from tables) and calculated columns (added through tables using DAX)
- Calculated tables are static, meaning that they will not depend on slicer selections or cross filtering, calculated tables will just refresh if the data model is refreshed
For this reason the following challenge has to be solved:
- A dynamic representation for the group member "Other" has to be created
The thinking behind the solution that is preseted is as follows:
- Create an unrelated table that contains all the members of the original table plus an additional member called "Other"
- Find the Top N members (in my example the Months) using the DAX function TOPN(…)
- Calculate the value for the remainder (all the others) using EXCEPT(…)
- Create a table that contains the TOPN and "Other" using UNION(…)
- Iterate over the intersection of the unrelated table and the calculated table
Here is the DAX statement that helps to visualize the remainder:
top 5 and other =
var top5 = CALCULATETABLE(TOPN(5,VALUES(Table1[Month]),CALCULATE(SUM('Table1'[Amount]))))
var other = ROW("Month", "Other")
var allTheRest = CALCULATE(SUM(Table1[Amount]), EXCEPT(VALUES('Table1'[Month]),top5))
var theUnion = UNION(top5,other)
var currentIterator = 'Unrelated'[Month]
'Unrelated'[Month] <> "Other"
,'Table1'[Month] = currentIterator
Here you will find a pbix file