Blog

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 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.

how to use DAX in Power BI to easily group or categorise numeric fields into ranges (sometimes called ‘bins’) below:

Image

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.

Image

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.

Image

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.)

Image

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]), COUNTROWS(FILTER(Customers, [TotalSales]>=VALUES(BinSelect[minvalueRange])&& [TotalSales]<VALUES(BinSelect[MaxValueRange]) ) ), COUNTROWS(Customers) )

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. 

Image

Like to know how this DAX measure works? DAX calculations are extremely useful for getting the most out of your data. At our public Power BI courses, we cover how to create these types of calculations plus a lot more. Our Power BI Fundamentals course covers the very basics of DAX, whilst our Advanced course goes further in-depth about more complex calculations. If you’re looking at just expanding your knowledge on DAX, we also offer a special one day course dedicated solely to DAX.

Not sure which course is best for you? Speak to our team today on 0800 0199 746 and we can help you understand which course would offer you the best value.