Categorising Measures into Numeric Ranges or “Bins”
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
Add new comment