Grouping in Power BI is often referred to as segmentation, chunking, binning, banding, or bucketing. For the purposes of this article, all will be referred to as the Dynamic Grouping DAX pattern.
For example, you may want to know the group totals of aged invoices in a certain time period. So, first ensure your grouping column is available in the fact table (e.g., Invoices[Aging Days]).
Aging Days =
SWITCH( TRUE(),
Invoices[Paid in Full] = 1, 0,
Invoices[Paid in Full] = 0, INT( TODAY() - Invoices[Invoice Date] ),
BLANK()
)
Then, setup a disconnected Supporting Table in your data model with groups, lower limits, and upper limits:
Then, you can use the Dynamic Grouping DAX pattern to group your invoices by the number of days they’ve aged:
Outstanding per Group =
// DAX PATTERN NAME: Dynamic Grouping
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
CALCULATE(
[Total Amount Outstanding],
FILTER(
VALUES( Invoices[Aging Days] ), // replace "Invoices[Aging Days]" with the column to be grouped
COUNTROWS(
FILTER( 'Aging Groups', // replace "'Aging Groups'" with the supporting table name
AND(
Invoices[Aging Days] >='Aging Groups'[Min], // replace "Invoices[Aging Days]" with the column to be grouped; replace "'Aging Groups'[Min]" with the lower limit of the supporting table group
Invoices[Aging Days] <='Aging Groups'[Max] // replace "Invoices[Aging Days]" with the column to be grouped; replace "'Aging Groups'[Max]" with the upper limit of the supporting table group
)
)
) > 0
)
)
NOTE: ensure that comparisons used in the FILTER clause of the DAX are appropriate for the upper and lower limits of the groups in your supporting table (i.e., if range values are discrete, use “<=”, or if range values are duplicated, use “<” or “>” as appropriate).
NOTE: This pattern can be achieved using either the disconnected supporting table approach described above or a calculated column approach, in which case a new column could be added to the Invoices table calculating the aging group; the formula for such a calculated column could be:
Aging Group (CC) =
SWITCH( TRUE(),
Invoices[Aging Days] = 0, "Paid",
Invoices[Aging Days] >= 1 && Invoices[Aging Days] <= 30, "1-30 days",
Invoices[Aging Days] >= 31 && Invoices[Aging Days] <= 60, "31-60 days",
Invoices[Aging Days] >= 61 && Invoices[Aging Days] <= 90, "61-90 days",
Invoices[Aging Days] >= 91 && Invoices[Aging Days] <= 120, "91-120 days",
Invoices[Aging Days] >= 121 && Invoices[Aging Days] <= 180, "121-180 days",
Invoices[Aging Days] >= 181 && Invoices[Aging Days] <= 10000, "Over 180 days",
BLANK()
)
DAX Patterns - Dynamic Grouping.pbix (88.0 KB)
