Use DAX To Segment & Group Data In Power BI

One comment

Using DAX measures to dynamically group data in Power BI is so powerful. You’re building out your data models and creating many different filters and ways to slice your data.

This is slightly more advanced today, but I love this type of stuff in Power BI.

Remember, these likely don’t exist in your original datasets, so you’re creating brand new insights that consumers of your content will really appreciate.

In this example, I show you how you can retrieve results inside a calculated column. You can then evaluate each of those results by running them through some parameters that you have in another ‘supporting table’.

Specifically, we may want to create groups out of our products and see what is the average profit margin we make.

underlying table

We might want to be able to see which ones are our  high margin products, mid-range margin products, and low range margin products.

Creating Average Margins

Let us first take a look at our data model.

With the tables that we have here, there is actually no way for us to group our products dynamically.

initial data model before working on how to group data in power bi

So what we have to do is to create a supporting table with the low, mid, and high margin product groups.

To do that, we’re going to start with our Average Margins calculated column.

In this model, we already have Total Profits which is the Total Sales minus Total Costs.

total profits measure

So, if we want to get Average Margins, we just have to create a calculated column and divide Total Profits by Total Sales.

Take note that in this image below, I have already changed the format of the Average Margins column into a percentage and sorted the column so that they are arranged from the highest to the lowest percentage.

3 calculated column2

Creating A Supporting Table

Now that we have created our Average Margins, we can proceed to creating our supporting table that will allow us to segment our products by the margins.

To do that, click on Enter Data at the upper left corner of the screen.

enter data

A pop up window like this will appear. We need to change the name and input columns.

pop up window to create table

In this case let’s call this supporting table Product Margin Group. And we’re going to have a Group column with Low, Mid, and High. Let’s also have a Min and a Max column.

So our low margin products will range from 30% up to 36%. For the mid-range margin products that would be from 36% to 38%. Then our high margin products will be from 38% up to 100%.

After entering all the necessary data, click Load.

creating the supporting table to

Now we have a supporting table.

data model with supporting table to group data in power bi

And we can also now use this Group with Low, Mid, High in our logic.

How To Segment and Group Data In Power BI

To work on our formula, let us create a new measure and call it Margin Group Profits.

We’re going to use CALCULATE here and then Total Profits so that we can have the total profits within the groups.

Margin group profits formula portion 1

Let’s jump down to another row and use FILTER to iterate the Average Margins table for us.

2 formula

We’ll jump down one row again then go COUNTROWS and then put in another FILTER. This time we’re filtering over our  Product Margin Group.

3 formula

Now, let’s go if Average Margins is greater than or equal to the Min and is less than the Max then we put in that table.

We’ll jump down to another row and add greater than zero.

Margin group profits formula portion

After closing off our formula, we can drag in our Group and add our Margin Group Profits.

As you can see, we’re now getting results here. We have total profits for our low, mid-range, and high margin products. And the overall total profits is 57.7 Million.

margin group profits table

We can do a quick look if that total is accurate by dragging in our Total Profits measure.

Both tables show the same value which somehow assures us that our formula is correct.

10 checing 1

But to be perfectly sure, we need to understand the logic.

Checking The Logic

Let us take a look again at the formula we created.

So first, we’re going to try to understand what FILTER does. This FILTER here created a table of every Average Margin Column.

1 filter

Then for every single row of that table, look through every single row of this table which is the supporting table that we created.

2 row

For every single row, check if it is greater than the Min and less than the Max. If it is, we’re going to retain that row.

3 min and max

Now, COUNTROWS is greater than zero here is saying that if that row is retained, it must be within that group.

4 countrows

Then this process is repeated for the next average margins.

This allows us to create these groups.

margin group profits table where we were able to group data in power bi

To better see which products are low margin, mid-range and high margin, we can turn the table we have created into a matrix.

And then we’re going to add Product Name into the Rows and the Group into the Columns.

3 product name.group

Our matrix will then look like this one below.

matrix as example of how to group data in power bi

Creating The Visualizations

Now, we can create our visualizations.

Let us drag our Group and Margin Group Profits.

1 creating visual

And then let us turn this table into a donut chart.

donut chart

Then let us drag in our Product Name and Margin Group Profits and turn this table into a stacked bar chart.

3 product name margin group

Now, after putting Group into the Legend, our visualization will look like this.

stacked bar chart showing how to group data in power bi

So the green bars are the high margin products, the light blue are the mid-range and the dark blue are the low margin products.

We can also make our visuals dynamic. Let us click Edit Interactions.

5 edit interactions 1

And then click on filter in the images that you want to be impacted.

dynamic visual 1 where we were able to group data in power bi

Then, if we click on High in our donut chart, our matrix and stacked bar chart will show the high margin products.

dynamic visual 2 where we were able to group data in power bi

And if we click Low, our matrix and stacked bar chart will then show the low margin products.

7 dynamic 2

Conclusion

In this post, we discussed how we can dynamically group data in Power BI using DAX.

After creating our Average Margins, we were able to create a supporting table and then we worked on our Margin Group Profits formula using CALCULATE. This enabled us to create really cool dynamic visuals.

When I use this type of technique my mind explodes with the opportunities to take your analysis to the next level.

Think about all the initial core calculations you might create like sales, costs, profits, margins, growth etc, and then think how you can segment your attributes or dimensions by any of these results.

All you have to do is create and run them through some type of logic using a similar technique that I showcase here.

This DAX logic isn’t easy, there’s no doubt about that, but I definitely want you to see how DAX can be applied to discover these insights.

Any thoughts or questions, let me know in the comments.

Good luck with this one.

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 *****
Advanced Segmentation Example Using DAX in Power BI
Group Customers Dynamically By Their Ranking w/DAX In Power BI
Learn Which Customer Groups Experienced The Greatest Growth – Power BI & DAX

***** Related Course Modules *****
Advanced DAX Combinations
Unique Analytical Scenarios
Learning Summit Series

***** Related Support Forum Posts *****
Data grouping and missing month
General Ledger Grouping
Dynamic Grouping – Last Month Value
For more grouping data support queries to review see here….

Enterprise DNA Events

1 comments on “Use DAX To Segment & Group Data In Power BI”

Leave a Reply

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