Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Binning and Grouping Data with Power BI


By:   |   Last Updated: 2017-03-03   |   Comments (2)   |   Related Tips: More > Power BI

Problem

Often when you speak with a DBA about grouping items into specific sets or binning data into certain groups, they will say, "Do it in the presentation layer" and not in your query. Power BI is definitely a great presentation layer, and it contains grouping and binning functionality to support just these sort of requests. The functionality can get a bit tricky when you begin to add multiple dimensions upon which to sort. In this tip we will cover both of these functions. Read this tip to learn more.

Solution

You may be wondering what the difference is between grouping and binning is? That is a great question; with grouping you are normally working with dimensional attributes whereas binning is generally grouping measure or numeric values into segmented buckets. Binning also gives you the opportunity to merge a numeric dimension into a predefined or rolled up group of values, often automatically. So, let us get started with some fine examples that utilize grouping and binning.

To get started with Power BI and Power BI Desktop, you need to do a few things to get everything setup for access. First, you need to go to the Power BI web site and sign up to access Power BI. Note that you must use a work or school address to sign up for Power BI. Next, you need to download the Power BI Desktop (PBID) tool. Once you have your account setup and your Power BI Desktop application installed, you are ready to start working with Power BI!

Grouping in Power BI

Let us get started by implementing a group. We are using the Wide World Importers databases, which is freely available from Microsoft, as the basis for our data. We will not go into all the details here ( feel free to review both this tip, Querying SQL Server Data with Power BI Desktop, and this tip, Power BI Data Import and Export with Excel, for details on retrieving data from a SQL Server or Excel dataset), but our first step is to load the Dimension Customer and Sales.CustomerTransaction tables using the Get Data functionality. We will use these tables as the source for our visualizations.

GetData in PowerBI

Next, we will create a simple visual with Customer, Transaction Amount, and Tax Amount.

simple visual in PowerBI with Customer, Transaction Amount, and Tax Amount

In the above screen print, you can quickly see the dilemma with the above list of customers; the Wingtip Toys Customer and TailSpin Toys Customer are broken out by individual stores; plus there is a large unknown customer list. This situation is the perfect reason to create a group. We will actually want to create two groups, one for Wingtip Toys and one for TailSpin Toys. To begin grouping, you would want to right click on the customer attribute in the Dim Customer dimension table and then select New Group. Alternatively, you can click on the three dot ellipse button next to the field, or you can click the down arrow next to the field in the values area.

New Group in Power BI

The Groups Window opens and we must complete the following:

  • Name of the new attribute that will reflect the groups. Note, this name is not the name of the individual groups, but actually the name of the field that will be created for the groups.
  • Select the values to be included in our group
  • Click the Group button as we complete each group
  • Decide if we want an "Other" Group
  • Finally click "OK" when all groups are setup
Complete the Groups Window in Power BI

Thus, each value for Tailspin Toys is selected and then the group button is selected.

Group Values in Power BI

Now we have our first group which includes all the TailSpin Toys customers.

Groups and Members in Power BI

Next, we group all the Wingtip Toys customers and also select the include Other group option. If this option is not selected, then all the values that are not in the groups are individually listed.

Include other Groups in Power BI

Finally, we click ok and our group is created; additionally, you will also notice that a new dimension attribute is created, called CustomerGroup. We can bring that group onto our visual, as shown below, and this attribute then acts as any other dimension field.

New Dimension CustomerGroup is available to bring into the visual

If we uncheck the include Other group, then any values not in our groups are listed individually, as shown below (Unknown is our only customer not grouped).

Unknown is our only customer not grouped

Finally, you may want to edit your groups. You can, as shown next, right click on the group or click on the ellipse next to the group field, and select Edit Groups.

Edit Group in Power BI

While editing the group, you may have noticed that the default names for the groups may not always be appropriate; they are actually set to the first value in the list group. However, if you double click on the group name (not the name field, but the actual individual group name), you can rename it. As shown below, I am highlighting "(Absecon, NJ) & ...".

Ranme Group in Groups Interface in Power BI

Once I delete this text, we now have a more appropriate individual group name.

Rename Result in Power BI

Binning in Power BI

Setting up Bins works in a fashion very similar to groups except the grouping only works on numeric, date, or equivalent type fields. The initial process is the same as for grouping, except after clicking the New group options, we are presented with a different grouping screen, as shown below. We still need to name our new Bin Group, but group type is now set to Bin; we can change this back to list if desired. Thus, in the below example, we are splitting the InvoiceID field in bins of 1000; that means that we will group the InvoiceID into groups of 1000 invoices.

Binning in Power BI

Thus, we now have a new attribute called InvoiceID (bins), which we can drop onto our visualization as shown below. For our InvoiceID example, we have over 70,000 invoices, but using Bins reduces the number of data points to just 70.

Bins in Power BI

If you want to make the new bin more useful and allow for drill down, we can now make a new hierarchy with the Bin field and our original Invoice ID field. To complete this task, we would first right click (or click the ellipse next to) our Bin field and select New hierarchy. Next, we would click on the base InvoiceID field, click Add to Hierarchy, and then select the InvoiceID (bins) Hierarchy.

New Hierarchy in Power BI

Add to Hierarchy in Power BI

Now we have a hierarchy that we can place on our cross tab visual.

New Hierarchy Result in Power BI

Now by dragging the hierarchy onto the visualization we can see the levels of the bin, as shown in the below illustration. I know what you are thinking, "Are we able to collapse the hierarchy. Unfortunately, not as of yet, but it is supposed to be on the Power BI road map! Hopefully soon.

Hierarchy with Bin in Power BI

Of course, we can use these same groupings on most of the other visuals available in Power BI, just by selecting a new visual from the Visualizations Gallery.

Chart with Bin in Power BI

Conclusion

Grouping and binning enhance the visual appeal of Power Bi Desktop / Online; this functionality allows you to allocate data values to specific groups for more distinct reporting. The grouping function provides the ability to hand pick which values go into which group, whereas the bin function requires you to pick the group size and then Power BI allocates each data point to a bin automatically. Binning, however only works with numeric or date data types. In either case, a new attribute or field is created and can be used just like any other field in your data sets.

Next Steps


Last Updated: 2017-03-03


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, December 21, 2018 - 4:34:38 PM - Scott Murray Back To Top

Unfortunately, you would want to create a custom column to create a  binn solution or hierarchy to use with specific names.


Friday, December 21, 2018 - 4:09:13 PM - Bob Back To Top

Is there any way to show the range? For example: 0-1000, 1000-2000, 2000-3000 instead of just 1000, 2000, 3000


Learn more about SQL Server tools