Power BI Banding & Segmenting Example Using DAX

No comments

In this tutorial, I go over a Power BI banding and grouping example using the DAX formula. This is quite an advanced example, but it does show an awesome way that you can visualize your reports.

Sometimes, you might want to visualize your reports in a certain way that shows a particular insight effectively. However, this would be quite hard to do in a traditional Excel report. On the other hand, creating compelling and unique visualizations like this in Power BI is very easy.

In this example, I work out my total sales for my customers in a particular year. But instead of just having this plain total sales per year, I want to see who among my customers grew a lot, those that didn’t, and the ones with average growth. This is what we call Power BI banding and grouping or segmenting.

power bi banding
Power BI Banding And Grouping Example

I work out a logic to achieve that, then I incorporate this logic to create comparison visualizations. I’ll show you how to do this from scratch.

Calculating Sales Growth

First, we want to work out Total Sales, obviously we start from here.

Then, we branch out to Sales LY (last year), which is a simple time intelligence calculation.

From here, we can work out Sales Growth because all we have to do is divide Total Sales by Sales LY, and subtract 1.

We can then create a table like this with our Total Sales and our Sales Growth so we can see the results.

But at the moment, this doesn’t really group, and so this is where we need to use the banding. This alone doesn’t group our customers by anything. In this case, we wouldn’t know what the growth was and which group these customers belonged to.

Creating The Bands Or Groups

To achieve this Power BI banding and grouping, we first need to create the groups or bands that we would classify as good growth, average growth, and bad growth.

To do this, we’re going to use the Enter Data feature. This is where we create our table, Sales Growth Groups.

Click on Enter Data to create a table.

We simply input the details that we want to have in this table’s columns, and then click Load.

power bi banding

This will just become a supporting table. It doesn’t integrate into our model at all. 

inside the data model

We now have our Customer Groups in this table.

power bi banding

With this, we can now create the logic or calculation to group all these customers.

Working Out The Logic For Sales Per Growth Group

To create a new formula or calculation, we click on New Measure.

Let’s call this one Sales Per Growth Group. We’ll go CALCULATE Total Sales to provide a different context. Then, in our FILTER logic, we’ll use VALUES Customer Name. We want to evaluate which row remains based on the growth of that customer, so we go COUNTROWS, and then FILTER our supporting table (Sales Growth Groups). Then, we’ll write our logic to run through this supporting table.

As we drag this formula into the Date context, nothing actually changes, which is exactly how it should be.

It’s meant to equal the Total Sales because we then have to overlay the context of our customer segments to get a breakdown. 

Overlaying The Context Of Customer Segments

We turn this table into a matrix like so to bring in our Customer Segments into columns. We can see that this breaks up based on the growth from year-on-year (YoY) growth for that particular customer.

power bi banding

From here, we can then create the visualization (click on the stacked bar chart and bring in Customer Segments into the columns) where we can look at each customer, but also have a clear delineation whether they’re good growth customers or poor growth customers. Simultaneously, we’re still highlighting Total Sales here.

Then, we sort it properly.

Now we have clear results.

power bi banding

We can also show these results (on the left table) as a bar chart with the custom banding inside it. We can have more insight than just the standard visualization.

power bi banding

Moreover, through this logic that we’ve developed, we can create a range of visualizations. We can create a scatter chart by utilizing this calculation.

With this logic, we can overlay our Customer Segments in the Legend. That’s going to break down the good versus bad growth customers, which brings that additional insight or element to our visualizations. 

Conclusion

This is a really powerful Power BI banding and grouping (segmenting) example. This is not the only way that you can use this technique. There are many ways that you can apply this technique. 

If you can understand the logic that I go through in this tutorial, you’ll go a long way. Think about what logic or banding you could do on any calculation and on any metric that you’ll calculate. It’s just a matter of setting it up and running through some very similar logic. 

All the best!

Sam

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

membership banner 3

***** Related Links*****
Use DAX To Segment & Group Data In Power BI
Segmentation Example Using Advanced DAX In Power BI
Data Segmentation Based On Percentage Groups – Advanced DAX In Power BI

***** Related Course Modules*****
Advanced DAX Combinations
Advanced Analytics In Power BI
Mastering DAX Calculations

***** Related Support Forum Posts*****
Banding Example When Using DAX In Power BI
Grouping And Banding In Power BI – Many Criteria – DAX Patterns
Banded Column Shading – Using Conditional Formatting?
For more banding queries to review see here…..

Enterprise DNA Events

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.