How can data binning be performed in Power BI?

By /

Data binning is a process of grouping a large set of data into smaller, more manageable categories or bins. This can be performed in Power BI through the use of the “Grouping” feature. By selecting the desired data fields and setting the bin size or range, the data can be automatically grouped and summarized into the designated bins. This allows for a clearer representation of the data and facilitates easier analysis and visualization. Additionally, Power BI also offers the option to manually create bins using custom formulas or parameters. This enables users to further customize the grouping of data based on their specific needs. Overall, data binning in Power BI provides a convenient and efficient way to organize and analyze large datasets.

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

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 do you perform Data Binning in Power BI?
  2. How to Perform Data Binning in Python (With Examples)
  3. How to Perform Data Binning in R (With Examples)
  4. How can I perform data binning in PySpark?
  5. How can a Left Join be performed in Power BI?
  6. How can Fuzzy Matching be performed in Power BI with an example?
  7. Fix: pandas data cast to numpy dtype of object? Check input data with np.asarray(data).
  8. How can INNER JOIN be performed with a WHERE Clause in MySQL?
  9. Can an Inner Join be performed on Multiple Columns in MySQL?
  10. How can a Kolmogorov-Smirnov Test be performed in SPSS?