How do you perform Data Binning in Power BI?

By /

Data binning is a process used in Power BI to group numerical data into categories or “bins” based on specified criteria. This can be done by creating a new column in the data table and using the “Group” function to define the bin size and range. For example, if we have a dataset with sales data for different products, we can bin the data by grouping sales into categories such as “low”, “medium”, and “high” based on their total sales amount. This allows for easier analysis and visualization of the data, as well as identifying patterns and trends within each bin.

You can use the following syntax in DAX to perform data binning on the values in a particular column:

Bin =
IF(
    'my_data'[Points] < 16,
    "<16",
    IF(
        'my_data'[Points] <= 20,
        "16-20",
        IF( 'my_data'[Points] <= 25, "21-25", ">25")
    )
)

This particular example creates a new column named Bin that returns the following values:

  • “<16” if the value in the Points column is less than than 16
  • Else, “16-20” if the value in the Points column is less than or equal to 20
  • Else, “21-25” if the value in the Points column is less than or equal to 25
  • Else, “>25”

The following example shows how to use this formula in practice.

Example: How to Perform Data Binning in Power BI

Suppose we have the following table named my_data in Power BI that contains information about various basketball players:

Suppose we would like to place each of the players into bins based on the number of points they scored.

To do so, click the New column icon:

Then type in the following formula into the formula bar:

Bin =
IF(
    'my_data'[Points] < 16,
    "<16",
    IF(
        'my_data'[Points] <= 20,
        "16-20",
        IF( 'my_data'[Points] <= 25, "21-25", ">25")
    )
)

This will create a new column named Bin that places each of the players into one of four different bins based on their number of points:

data binning in Power BI

From the output we can see:

  • The first player who scored 22 points is placed into Bin 21-25
  • The second player who scored 14 points is placed into Bin <16
  • The third player who scored 19 points is placed into Bin 16-20

If you’d like, you could also return strings as the bin values.

For example, you could use the following formula to place each individual player into the bins of “Bad”, “OK”, “Good” or “Great”:

Bin =
IF(
    'my_data'[Points] < 16,
    "Bad",
    IF(
        'my_data'[Points] <= 20,
        "OK",
        IF( 'my_data'[Points] <= 25, "Good", "Great")
    )
)

This will create a new column named Bin that places each of the players into one of the four bins based on their number of points:

Note: In these examples we chose to use three nested IF functions to place each player into four bins, but you can use even more nested IF functions if you’d like to place the players into even more bins.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

x

Related terms:

  1. How to Perform Data Binning in Python (With Examples)
  2. How to Perform Data Binning in R (With Examples)
  3. How can I perform data binning in PySpark?
  4. Can I perform advanced data analysis with Power BI using SUMMARIZE and FILTER?
  5. Fix: pandas data cast to numpy dtype of object? Check input data with np.asarray(data).
  6. How can data be normalized between 0 and 1 in Power BI?
  7. Excel: Convert Monthly Data to Quarterly Data
  8. Excel: Convert Quarterly Data to Annual Data
  9. How to Perform Power Regression in Excel (Step-by-Step)
  10. How to Perform Simple Linear Regression in Power BI?