How to group data into ranges in Power BI (banding values)

In this article we will show you a quick and easy way to group your data into ranges or bands in Power BI.

This can be useful if you want to create visuals such as pie charts or bar charts and include the bands in these.

Follow the two steps below to add banding to your Power BI report.

Step 1: Create a Banding table

Here is the example data we will be using in this tutorial:

It is a table listing items, when they were shipped and when they are received.

We have created a DAX custom column using DATEDIFF to show the number of days between the two dates.

We want to group the data into ranges depending on the difference.

For example, if the difference between shipped and received is 7 days or less, we want to return “1 to 7 days”. If it is between 8 and 14 days difference, we want it to return “8 to 14 days”, and so on.

To group the values into the bands we want, we need to create a banding table with the name of the band, the first value and the end value for each one.

Here is our banding table:

Step 2: Create a DAX custom column to group data into bands

Now, we need to create a custom column to return the correct band based on the value.

In your original table (the items table in our example), navigate to ‘Table tools’ on the top ribbon and select ‘New column’.

Here is the DAX formula we have used to create our banding column:

Band = 

VAR ValueToCheck = Items[Difference]

VAR BandingName = CALCULATE(
    MAX(Bands[Band]),
    FILTER(Bands,ValueToCheck >= Bands[Band Start] && ValueToCheck <= Bands[Band End]))

    RETURN IF(ISBLANK(BandingName),BLANK(),BandingName)

You can also just use the section highlighted in bold for your custom column (as long as you replace the variable with the relevant column, i.e. change ValueToCheck to Items[Difference]) and it will return the bands.

In our example, using either the full statement or only the bold section returns the same values, as below:

We have used variables in the full statement to first make the main statement easier to view and secondly so that we can incorporate an IF statement that will ensure no errors are returned if there are blank values in any of the date fields.