Grouping and Binning; Step Towards Better Data Visualization

2016-11-03_07h49_09

The latest update of Power BI Desktop (October 2016) has many features. Two of these features are grouping and binning. These features used to create groups of items and visualize them better in the report. Previously you could do that by Power Query or DAX calculated columns, now it is all possible simply through the graphical user interface of report editor. In this post I’ll show you how to create banding (binning) and grouping simply with these features. If you like to learn more about Power BI; read the Power BI online book from Rookie to Rock Star.

Prerequisite

For running examples of this post you need to have AdvantureWorksDW SQL Server database sample installed. Or you can get the excel version of it.

Get Data; Start with Get Data from AdventureWorksDW, and choose tables: FactInternetSales, DimDate, and DimCustomer.

Set the relationship between DimDate and FactInternetSales to be only based on OrderDateKey as Active relationship, remove inactive relationships between these two tables.

Banding or Binning

Binning is grouping a numeric field based on a division. This type of grouping is called Banding as well. For example you might have customers with different yearlyIncome range from $10,000 to $100,000 and you want to create a banding by $25,000. This will generate 4 groups of yearly income for you. This is exactly what Binning in Power BI does. Let’s look at the example.

Create a Table in Power BI Report and visualize YearlyIncome (from DimCustomer), and SalesAmount (from FactInternetSales) in it. Change the aggregation of YearlyIncome from Sum to Do Not Summarize as below

2016-11-03_07h38_48

This will visualize each yearlyIncome value with the sales amount associated to it.

2016-11-03_07h41_06

Now let’s create groups of people based on their yearly income categorized in buckets of $30,000. Right click on the YearlyIncome column in the fields section and select Group.

2016-11-03_07h43_11

In the Groups window make sure Bin is selected as the group type (by default for numeric or date/time columns this will be the default). and set the size to 30000. This is the size that values of YearlyIncome will be split based on it.

2016-11-03_07h46_03

Press OK in the Group Window, and bring this new generated field (named YearlyIncome (bins)) to the table.

2016-11-03_07h49_09

Color coding above shows that YearlyIncome values grouped by the division result of the value of yearly income by group size which is $30,000. If you want less groups you can increase the group size, or if you want more detailed groups you can decrease the group size.

Now you can remove the YearlyIncome from table view, change the table to be Column Chart to see which group generates the most revenue.

2016-11-03_07h54_51

This binning can be applied to datetime values as well. For example if you want to see revenue based on half year’s banding, you can simply create a group with Bin size of 6 by Month. Let’s see the example; Right click on FullDateAlterneteKey column (from DimDate), and create the group;

2016-11-03_08h08_12

Now create a Bar chart with this new column (named Oder Date – 6 Months), and Sales Amount.

2016-11-03_08h09_14

You can simply create other banding or binning for Years, Months, Days, Hours, and Minutes as well.

Grouping

You Can Create a group of multiple items in a visualization. This group can be simply created by Ctrl+Click and selecting items, then grouping them. As an example; create a Column Chart with English Education (from DimCustomer), and SalesAmount (from FactInternetSales).

2016-11-03_08h26_13

Now Ctrl Click and select High School and Partial High School, then right click and select Group.

2016-11-03_08h27_20

You will see now them grouped together with a legend on the top of chart showing this group, and an “other” group.

2016-11-03_08h28_34

Now create another group for Bachelors and Graduate Degree in the same way.

2016-11-03_08h30_51

You have now three groups; higher education (Bachelors and Graduate Degree), College (Others), and High School (High School and Partial High School).

Once you have the grouping you can do many nice visualizations with that, an example can be creating a Stacked Column Chart with These Groups as the Axis, and the English Education as the Legend. To do so; Change these two in the chart and change it to be Stacked Column Chart.

2016-11-03_08h39_38

Now the chart will look like this;

2016-11-03_08h41_03

This shows simply how much revenue each category generates in total, and also inside the category which sub category (English education) generates the most or least revenue.

Behind the Scene

What is happening behind the scene is that there is a DAX calculated column (calculation is not visible in Power BI) created for each binning. This was exactly the method of implementing such thing previously. There is also another method which is creating the banding through Power Query.

Grouping and Binning is part of your model. That means if you create another report or chart you can use the Group there. Even if you create calculated tables or any DAX calculations you can use these new Group columns there.

Summary

There are heaps of possibilities that these new grouping features bring to your visualization. Having these grouping as part of you model also is great to have unique experience in your reporting solution. However There are always complex grouping conditions that still need to write a bit of DAX code or Power Query M script.

Save

Save

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

6 thoughts on “Grouping and Binning; Step Towards Better Data Visualization

  • Grouping and Binning are indeed powerful features. Unfortunately, this functionality is not available for any Analysis Services connection. From what I understand, this is because the “model” doesn’t exist in Power BI and all reporting/analysis is dependent on the model created within SSAS. This is not the only feature that we are blocked from accessing (for example, we can’t add calculated measures either). Are you working on providing support for your Power BI Grouping and BInning for SSAS connections in the future? Is there any kind of workaround that would let me use this feature directly within Power BI?

    • Hi Peter.
      Thanks for your comment. As you mentioned these features are not available in LiveQuery to SSAS. I am not working for Microsoft, so definitely I am not working on providing this feature. But I believe Microsoft team would have many new features in their road map which will come at some stage ( I don’t know any dates unfortunately)
      At the moment the work around is: When you use Power BI on top of LiveQuery SSAS, do all modeling inside SSAS. You can create grouping, binning there, but not through GUI, with DAX expressions with calculated columns and measures. And then you can use it in Power BI for visualization.

      Cheers
      Reza

  • How to Create a binned column where each bin has the same number of unique values as the others.

    • Hi Gaurav
      You can create bins with saying that you want the size of bin to be something, or based on the bin total count. For example, let’s say values are from 10 to 100, incrementing 10 each time. You can say that you want 5 bins. this means each bin will have two of these values. or you can say you want the size of bin to be 50. for your scenario, binning based on the count would work better I believe.
      Cheers
      Reza

Leave a Reply