Create Customized Age Bins (or Groups) in Power BI

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
Create customized age bins in Power BI

Power BI has the built-in feature of creating binning for a numeric field such as age. However, the default binning will create bins of equal size. If you want to create bins of different sizes, then you need to create that as a custom column. Fortunately, Power Query has transformations that can help. In this article, I’ll explain how you create customized age bins in Power BI.

Video

Sample data

The sample data table which I use here, has ages from 35 to 105. These are ages of customers, and besides it I have the sales amount generated from those customers.

Sample data of ages

The ages of this data table are calculated from a birthdate column, using the method that I mentioned here. I recommend reading about it if you first need to calculate the age.

Age calculated from the birthdate column

Follow this article to learn more about it:

The required report

I want to have a visual that shows age bands like below with the total sales amount from each;

the desired age bin output

As you can see bins are in different sizes, from bins of five years apart (such as 35-40), to bins with 35 years range (70 and above).

The built-in binning in Power BI

One of the first stops for binning and grouping in Power BI is the built-in functionality to do that. But the problem with that method is the the bins created using this approach will have similar sizes. If you like to know more about binning with equal sizes, check out my article here;

Dynamic binning using DAX measures

I also explained another example of binning using DAX measures in two separate articles, in one you can choose the size of the bin, and in another one you can choose the number of bins. Both of these methods are useful when you want to dynamically change the bins.

Choosing the count of bins dynamically using DAX measures

Choosing the size of bins dynamically using DAX measures

The method we want to use here can be much simpler, and doesn’t require DAX calculation. It can be done using a conditional column in Power Query.

Power Query Conditional Column

Conditional column in Power Query is a way to create a new column based on some conditions. The conditions can come from expressions that take other columns into consideration. I have explained in detail how the conditional column can be used in this article:

We are going to use the same approach here. You can go to the Transform Data to get the Power Query Editor open.

Transform Data in Power BI Desktop

Then click on Add column, and then Conditional Column.

Add conditional column in Power BI and Power Query

The conditional column graphical interface is simple and powerful to use. You can for example set conditions like below;

creating customized age bins using conditional column in Power BI

You may ask; “what we don’t use age is greater than X and also less than Y? wouldn’t this double count some?” The answer is no. You don’t need that for this calculation. There is an important part in the calculation and that is the ORDER of conditions and ELSE in the condition.

How the conditions work in conditional column of Power BI and Power Query

In the example above, the first condition to check will be if the Age is greater than or equal to 70. If there is someone with the age of 72, it will be definitely in this group, but not someone with the age of 65. Then the next condition will be checked for only the group that did not meet the previous condition. That is why we have ELSE If. I have more explanation about how it works in this article if you are interested.

Once you created the new column, you can load the results into Power BI using Close and Apply in the Power Query Editor, and then use the new Age bin column to visualize the data

The new Age bin column used in a visual in Power BI

Sorting

If your Age bin values are not sorted in the way you want it, then you need to add a sort order column to the solution. I have explained that method here in the article below;

Summary

In summary, there are many different ways of doing age binning in Power BI. Depends on if you want equal size bins, or custom sized, if you want the solution to be dynamic or static, there are different methods for it. In this article, I explained how to use the conditional column in Power Query to create static but custom variable sized age bins in Power BI. I do recommend to read articles below as further study options:

FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply