Manual Groups and Bins in Power BI: Why and How

When needed, Power BI offers the ability to create Groups and Bins to help consolidate data into a more presentable or meaningful visualization. The documentation has more detail and walks through the process of creating Groups and Bins. Looking at Bins and Groups in a Power BI model using a tool like SQL Server Management Studio, there is no magic behind them. They are implemented in DAX. This means that you could recreate a lot of what you would typically do in the UI with Power Query or DAX.

Why? In most circumstances, the built-in Groups and Bins will be fine. In more advanced cases, you can:

• Have more control over the output than what the Power BI Desktop’s interface allows
• More easily bottom-code or top-code a distribution of values to obfuscate left or right tails
• Streamline what normally takes numerous clicks to create
• Have an easily maintainable sort order when putting text fields on an axis

SUMMARY

Since this is a rather long post with many screenshots, here is a summary of some DAX formulas that are directly based on what Power BI includes in its model. The bins include some conditional statements to account for top-coding and bottom-coding.

This manual setup allows you to quickly get to a result such as the following, which combines uneven group membership and custom sorting.

DETAIL

If you would like to step through some of the process, the rest of the post contains more detail. Here is a histogram showing a frequency of fake test scores ranging from 59 to 97.

Adding a new Group for this data with a Group Type of Bin and Bin Type of Size of bins creates a floor based on the bin size. With a bin size of 5, for example, the value 59 will appear in the bin labeled 55, values 60 to 64 will appear as 60, etc.

Here is the same visual with the bins on the axis instead of each individual value. Size of bins equals 5.

Here’s how it appears if you change Size of bins to 10.

Instead of using the Size of bins option, you can also select Number of bins.

Assigning a Bin count of 10 alters the axis based on the minimum and maximum values. Note how the axis now starts on 59 when using Number of bins (the lowest actual data value).

In addition, you can create Groups either using your original data or based on your bins. For example, perhaps I want to use bins with Size of bins equal to 5. Using a Group Type of List, I then add bin values from 70 to 85 into a “Pass” group, values 90 and 95 to a “Superb” group, and everything else to an “Other” group. I can setup groups like the screenshot below. In case you did not know, you can double-click on “Other” as with any other group name and change how it is labeled.

UNDERLYING DAX

Taking a peek at the tabular model script, Power BI implements Groups and Bins in DAX. Here are my bins based on Size of bin equal to 5.

Here are bins based on Number of bins equal to 10.

The Groups implemented when I assigned my bins to the Pass, Superb, or Other groups appear below. Note the use of DAX’s SWITCH() and IN. Also, see how the “Other” group is handled in the SWITCH() and can be renamed to whatever you might want besides “Other”.

RECREATING DAX

That’s easy enough to recreate manually (this DAX formula does not include the check for blanks).

The results are the same as using Power BI’s UI to create the bins.

That’s great, but why go through the trouble of manually doing that now that Groups and Bins are available in the interface? Perhaps you want to bottom-code or top-code your distribution to help obfuscate the data a bit. In my case, I want to bottom-code data so that values less than or equal to 70 appear as 70, and values greater than or equal to 90 appear as 90. It’s easy enough to create Groups in the UI labelled as <= 70 and >= 90.

SORTING MANUAL GROUPS

Note how they sort though. Since the axis is a text field, the >= and <= impact sort order.

Normally, this is fixed by changing the field for the Sort By Column on the Modeling tab. That will only work if the number of values in the group field matches the number of values in the sort field. Otherwise, Power BI will not allow it and provides a warning message.

My thought process here (which will not mirror everyone’s) is “Why use the UI for creating the bins and groups for more advanced scenarios when you end up having to manually create a Sort column anyway?” I now use the built-in Bins and Groups for basic scenarios and jump to manual bins and groups when needed.

It involves two columns: one for the label as it appears in the visual and one for the sort order. They are the same logic but only display differently. Apply the Sort column to the Label column using Sort By Column on the Modeling tab.

These fields provide a method to get the following result, which is more difficult to obtain in the UI.

ALTERNATIVE IN POWER QUERY

If you frown upon creating all these columns in DAX because of a stigma about it being too amateur to use calculated columns, handle it in Power Query instead. Just remember that the built-in functionality creates DAX columns.

Table.AddColumn(ABC, "TopPQ", each if [AssessmentRawScore] >= 90 then ">=90" else [AssessmentRawScore])

Table.AddColumn(ABC, "TopPQSort", each if [AssessmentRawScore] >90 then 90 else [AssessmentRawScore])

Table.AddColumn(#"Added Conditional Column", "BottomPQ", each if [AssessmentRawScore] <= 70 then "<=70" else [AssessmentRawScore])

Table.AddColumn(#"Added Conditional Column", "BottomPQSort", each if [AssessmentRawScore] <= 70 then 70 else [AssessmentRawScore])

In summary, Power BI implements Groups and Bins in DAX. In some cases, there may be a need or convenience to go beyond what is offered in the UI. In those cases, you can take advantage of the same or similar DAX formulas that Power BI uses under the hood, or you can attempt to create your Groups and Bins in Power Query.