Grouping and Banding makes analysis easier and convenient
Consider for example this table of 250 billionaires taken from www.forbes.com/billionaires/list
Click here to Download Excel file
As an Analyst you might wonder how many of these are Young, Middle Aged, Old, Very Old. So you develop a criterion for Grouping them
| GROUP | Minimum Age | Maximum Age |
| Young | 1 | 40 |
| Middle Age | 41 | 50 |
| Old | 51 | 65 |
| Very Old | 66 | 100 |
Following is the Pivot output you desire
| No of Billionaires | Total Net Worth | |
| Young | 7 | 108 |
| Middle Age | 28 | 340 |
| Old | 76 | 1,048 |
| Very Old | 139 | 2,052 |
| Grand Total | 250 | 3,547 |
PROBLEM
The problem here is that the two Tables “Billionaires” and “Age Groupings” cannot be connected to each other via a relationship because there is no common Field/Column between them
SOLUTIONS
We will solve it two ways using DAX power. The most crucial function here is FILTER.
FILTER is an ITERATOR in DAX. Its beauty in this situation is that it allows two columns in two separate tables to be compared against each other even when there is no defined relationship between them
Here are the solutions
1) Calculated Column in Billionaires’ Table that will give the Age Group against each Billionaire
The DAX formula that will do this is given below
=CALCULATE (VALUES (AgeGroup[GROUP]) ,
filter ( AgeGroup,
Billionaires[Age]>=AgeGroup[Minimum Age] && Billionaires[Age]<=AgeGroup[Maximum Age])
)
Lets look at how this formula works
Remember the filter expression in CALCULATE is evaluated first. So for each row in Billionaires Table, the FILTER function i.e.
filter ( AgeGroup,
Billionaires[Age]>=AgeGroup[Minimum Age] && Billionaires[Age]<=AgeGroup[Maximum Age])
goes to the AgeGroup Table, iterates over each row (4 rows of the Table) and fetches a Table with rows where the criterion are met i.e. Billionaires[Age]>=AgeGroup[Minimum Age] && Billionaires[Age]<=AgeGroup[Maximum Age]
Since the Age Groups do not overlap only one row is returned each time. That is to say each Age can fall in only one of the Groups (Young, Middle Age, Old, Very Old).
Remember the power of VALUE function. VALUES function on a Single Column with Single Row returns a Scalar Value. So VALUES (AgeGroup[GROUP]) returns the appropriate AGE Group for each billionaire
2) Calculated Column in AgeGroup Table that will give the Total Net Worth for each Age Group
we can do it another way around. Instead of creating a calculated column in Billionaires Table, we can create a table in AgeGroup Table and then filter the Billionaires’ table for each Age Group
The DAX formula that will do this is
=calculate(sum(Billionaires[Net Worth]),
filter(Billionaires,
Billionaires[Age]>=AgeGroup[Minimum Age]&&Billionaires[Age]<=AgeGroup[Maximum Age])
)
Lets look at how this formula works
Remember the filter expression in CALCULATE is evaluated first. So for each row in AgeGroup Table, the FILTER function i.e.
filter(Billionaires,
Billionaires[Age]>=AgeGroup[Minimum Age]&&Billionaires[Age]<=AgeGroup[Maximum Age])
goes to the Billionaires Table, iterates over each row (250 rows of the Table) and fetches a Table with rows where the criterion are met i.e. Billionaires[Age]>=AgeGroup[Minimum Age]&&Billionaires[Age]<=AgeGroup[Maximum Age]
sum(Billionaires[Net Worth]) adds up the net worth in the filtered table received above