DAX: How to group measures into numeric ranges

By Burningsuit

DAX

DAX (Data Analysis Expressions) is a useful library of functions used across Power BI, Excel and SQL. Stuart Box, Burningsuit's owner and trainer who is both a Microsoft Certified Professional and Member of the Learning and Performance Insitute, explains how to use DAX in Power BI to easily group or categorise numeric fields into ranges (sometimes called 'bins') below:

How to group numeric fields

  1. Firstly, right click on the numeric field in the field list, as select “group”.
  2. Once grouped, you can then use these groups (or bins) to count how many values fall into each bin, create "totals" for each bin etc.

Is it possible to create 'bins' to categorise the result of a measure?

I was recently asked if it was possible to create 'bins' that categorise the result of a measure, specifically if it was possible to group the total sales value for each customer (as opposed to grouping individual transaction values) into various numeric ranges. The good news is, yes, it is possible to do this!

In the example below, you can see the BIN Ranges I created. Notice how these specify the minimum and maximum range for each bin as well as the name for each range. These are named "MinValueRange" and "MaxValueRange".

In the Bin Results table, the “No of Totals for Customers” measure calculates the number of Customer Sales (using a measure called “TotalSales”) that fall into each bin e.g. there are 10 sales with a total between 1,000 and 4,999. You can cross check this in the Customer Sales table visual that shows the “TotalSales” measure.

How did I do this?

Firstly, my Data Model has a Customer’s dimension table related to the Winesales Fact Table, as shown here. 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.

Next up, I created a table that holds my Bin names and Bin ranges. To do this, you could use an Excel Table, or use the “Enter Data” option on the Home Tab. (Note that I used the “Index” column to correctly sort the RangeName Column in the table visual - use the “Sort by Column” command on the Modelling Tab.)

I called this table “BinSelect”.

When I loaded this table into the Data Model, I didn’t relate it to any other tables.

Now, remembering the measure I want to put into my bins is called “TotalSales” and my Fact Table is called “WineSales”, I created the “No of Totals Customers” measure to count the number of customer’s sales in each bin, as follows:

No of Totals Customers =
IF (HASONEVALUE (BinSelect[minValueRange]),
CALCULATE (
COUNTROWS (Customers),
FILTER (Customers,
[TotalSales]>=VALUES (BinSelect[minvalueRange])
&&[TotalSales]
), Winesales),
CALCULATE (COUNTROWS (Customers), Winesales)
)

To see the bin results, I created a table visual and placed the Range Names in the first column, then put the “No of Totals Customer” in the second column. 

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 our 2-day Power BI Service Training course or our 1-day DAX for Power BI Training course?"

Leave a Reply