How to Perform Data Binning in Power BI (With Example)


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

And so on.

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:

How to Count Number of Occurrences in Power BI
How to Use “If Contains” in Power BI
How to Write an IF Statement with Multiple Conditions in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *