Categorising Measures into Numeric Ranges or “Bins”

By Alison Box

DAX Power BI in Practice

We’ve often been asked if it’s possible to group the results of a DAX measure into numeric ranges. For example, to group a Total Sales measure for each Customer into various numeric ranges, sometimes referred to as “bins”. The answer is, yes it is, as you can see from the screenshot below:-

In this screenshot, we can see that we have five customers whose Total Sales fall between 150,000 and 199,999 in 2019.

How did I do this?

Firstly, my Data Model has a Customer’s dimension related to the Sales Fact Table, as shown here. (So, if you wanted to create bins for your Product Totals, you would need to have a Product Dimension, for Regions, a Regions Dimension and so on)


The measure I want to put into the bins is called “Total Sales” and uses this expression:-

Total Sales =SUM (Sales[TOTALSALES] )

Next up, I created a table that holds my Range names and Bin ranges. To do this, you could use an Excel Table, or use the “Enter Data” option on the Home Tab.

So it appears correctly sorted in the Table Visual, you’ll need to sort the Range Name column by the Min Value column. To do this, use the “Sort by Column” button on the Modelling tab.

I called this table “Bins” and it’s not related to any other tables in the Model.

The measure for “No of Customers with these Total Sales” is as follows

To see the bin results, I created a Table visual and placed the Range Names in the first column, then put the “No of Customers with these Total Sales” in the second column. I then used a slicer to slice by 2019


Like to know how this DAX measure works?

We can show you how to create these types of calculations using Power BI Desktop and a whole lot more. Why not attend one of attend one of our Power Bi Training courses

5 Comments

  1. I agree with Philip! Thank you! Now my question is if I wanted to calculate the percentage of each range compared to the total, what would that measure look like?

  2. Hi Chandler
    To find the percentage each range is of the total, you can edit the above measure like this and then format as per cent:-

    No of Customers with these Total Sales Per Cent =
    SUMX (
    ‘Bins for Sales’,
    DIVIDE (
    COUNTROWS (
    FILTER (
    Customers,
    [Total Sales] >= Bins[Min Value]
    && [Total Sales] < Bins[Max Value]
    )
    ),
    DISTINCTCOUNT ( Sales[CUSTOMERID] )
    )
    )

    Hope this helps.

Leave a Reply

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