Creating aging buckets in Power BI is quick and easy the create group feature. Power BI will automatically split values into groups for presentation and analysis. The functionality is especially useful when setting up dashboards for accounts receivable aging and other use cases when you want to put values into bins.
Binning in Power BI is a crucial technique for quickly analyzing datasets. While binning is a subset of grouping in Power BI it serves a different purpose, and is most commonly used for numeric analysis, such as accounts receivable aging. We’ll explain how to setup binning using Power Query, DAX, and no coding at all.
Let’s dive in!
Table of Contents
What is Binning in Power BI?
Binning in Power BI refers to the process of grouping data into smaller categories or bins. Binning is particularly useful when dealing with large datasets. It allows for easier analysis and visualization. One of the primary benefits of binning in Power BI compared to other systems is the ability for the end report user to drill into the detail of a bin and view individual records that are contained in it.
By categorizing data, binning helps identify patterns, trends, and outliers. Bins are often used in matrix visuals but can also be deployed using various graphs for easier visualization. The techniques can be applied universally across most visuals.
There are several methods for creating bins in Power BI that range from a built in binning function, utilizing Power Query to writing custom DAX formulas.
Calculating the Days Between Dates in Power Query
If you are using Power BI to present aging schedules that are binned by number of days, it can be helpful to calculate the difference between two date columns using Power Query. Check out our comprehensive write-up on calculating date differences using Power Query.
The basic premise is to create a column using the Duration.Days formula in Power Query instead of DAX formula because the Power Query will generate specific rows of values that can be grouped and binned. However, you could also use a DATEDIFF DAX formula and generate a new column which may not be as efficient when a report viewer executes a query to explore a visual.
How to Automatically Create Bins in Power BI
Creating bins in Power BI can be done automatically through the Power BI interface. This feature is especially handy when you need to categorize numerical data quickly. To do this, you simply need to:
- Select the column you want to bin in your dataset.
- Right-click and choose the New Group option.
- Define the bin size and range, and Power BI will automatically group your data into these specified bins.
Pre-Requisite Calculate the Days Between Dates
The following screenshot is our starting dataset that calculates the number of days between two date columns in Power Query. Before you close Power Query, make sure to define the data type as Numeric so Power BI can calculate the appropriate bins in the future.
If you already have a numeric column established in your dataset, this step is not necessary.
Creating a New Group for Binning in Power BI
To create a bin, right click on a column from the data panel of the report builder in Power BI and select New Group. Even though grouping is used to manually assign categorical and text data to groups, Power BI will detect the numeric data type of the column and provide options specific to numeric binning.
The Group editor has two different ways to create bins. You can manually define the number of bins, or you can define the size of bins.
If you’re trying to setup specific groups, such as 30 days, 60 days, 90 days, you will want to define the binning based on Bin Size. In this example, we would say bin size = 30.
Our dataset from sales to invoice date has a small range in days, so we will generate a bin size of two.
When defining the bin size Power BI will automatically create bins starting at zero. For our dataset, the result will be 3 bins for the range of a min value of 1 and a max value of 4. The resulting bins will be 0 – 2, 2 – 4, and 4 which represent the 3 possible buckets that a numeric value could fall into.
The other option is to automatically allow Power BI to generate bins based on a number of buckets, and
Once you click OK, the New Group will be created that will show up like a measure or field on the data tab for the table that was selected. Add the group to a visual like you would any other field value.
Creating Custom Bins with Power BI DAX
For more control over your binning process, you can use Power BI’s Data Analysis Expressions (DAX) to create custom bins. This method involves writing DAX formulas to specify the binning logic. For example, you can write a
Expanding on the use of Data Analysis Expressions (DAX) in Power BI for custom binning, you can create bins based on specific value ranges, conditional criteria, or even dynamic calculations. DAX offers a powerful way to create sophisticated binning logic that can cater to complex business needs. Here are a few specific formulas and approaches you might consider:
Creating Power BI Bins with the DAX SWITCH formula
To create bins within specific value ranges, you can use the
SWITCH function. However, it must be used with a measure that’s already aggregating values. This method is appropriate for times when you want to determine if the aggregate values of a condition need to fit into a specific bin.
For example, if you have a column named ‘SalesAmount’, you can create a calculated column for binning like this:
Sales Bins = SWITCH(
'TableName'[SalesAmount] <= 1000, "0-1000",
'TableName'[SalesAmount] <= 5000, "1000-5000",
'TableName'[SalesAmount] <= 10000, "5000-10000",
This formula assigns each SalesAmount to a bin based on its value, where SalesAmount is a previously created measure.
Creating Power BI Bins with the DAX IF formulas
A less eloquent but equally useful way to assign values to bins using DAX is the use of nested IF statements. We would generally avoid this method because it’s more difficult to read than a switch, but if you do need to create many different buckets you can use this method and copy and paste rows of formulas to avoid having to manually type it all out.
Creating Bins in Power BI is quick and easy when you calculate the number of days between dates using Power Query. This sets you up to right click on the column name in the report view data panel and use Power BI’s built in Group By feature.
While grouping is often used to assign text values to categories, when you launch it with a numeric value selected you’ll be presented with options to automatically bin values based on specific ranges, or to equally split the data into an equal number of bins.
An alternative method is to use a DAX SWITCH formula but requires values be aggregated with a measure prior to binning them. Each of these are slightly different use cases but can be quite useful depending on what level you want to bin values at.