Power BI Group by Column [With Various Examples]

This Power BI tutorial explains how we can group by column and display the values using group by function. Additionally, we will also cover the below-mentioned topics:

• Power BI Group by Dax
• Power BI Group by column and sum
• Power BI Group by column and count
• Power BI Group by column in table
• Power BI Group by column and concatenate values
• Power BI Group by column in visual
• Power BI group by column from another table

Also, Read: Power BI DAX ISBLANK vs ISEMPTY

Power BI Group by Value

Let us see how we can group the values by column with an example in Power BI.

In this example, we are going to use the Superstore sample Data displayed below to group the values using the New group option in Power BI.

• Open the Power Bi Data and load the data using the get data option, Select the column field that you want to group by.
• Right-click on the selected column field and click on the new group option as below: Here I am going to group the values for the Category column field.

• In the new group pop-up window, enter the group name and select the group type as a list and select the value of the group and click on the group option.
• In the same way, select the ungroup option to ungroup the value, and to include other groups click on the include check group option.
• Click the Ok button to save changes.

• The screenshot below displays the newly added column with the grouped values. In the Furniture category, it groups the Furniture.
• In the same way, it groups technology and the office supplies category it displays the others group category because the selected category value is not grouped.

This is how to group the values by column using the new group option in Power BI.

Check out: How to Convert Decimal to Text in Power BI

Power BI Group by Dax

Here we will see how we can group the data by values using the Power BI Dax in Power BI.

In this example, we are going to use Power BI Data to group the sales data value based on the condition applied.

• Load the data using the get data option, once the data has been loaded. Select the New Column option under the modeling tab.
• And then apply the below-mentioned DAX formula to group the values.

Group = 
IF (
   Orders[Sales] < 500,
    "<=500",
    IF (
      Orders[Sales] > 500
            && Orders[Sales] <= 1000,
        "500-1000",
        IF (Orders[Sales] > 1000 && Orders[Sales] <= 1500, "1000-1500", ">2000" )
    )
)

where,

1. Group = Newly created calculated column
2. Orders = Table Name
3. Sales = Existing column name

In the below screenshot, you can see that the newly added calculated column displays the result as the grouped values based on the condition applied.

This is how to group the data by values using the Power BI Dax in Power BI.

Also Read: Power BI Group by column and sum

Power BI Group by column and count

Here we will learn how we can group the columns and count the values using a distinct count function in Power BI.

In this example, we will use the Power Bi distinct count to the group values by segment and count the sales values in Power Bi.

• Once the data has been loaded into the Power Bi desktop. Select the New Measure option under the modeling tab.
• And then apply the below-mentioned DAX formula to group the values and count the values.

DistinctCount = CALCULATE(DISTINCTCOUNT(Orders[Sales]), GROUPBY(Orders,Orders[segment]))

where,

1. DistinctCount = Newly created measure
2. DISTINCTCOUNT& GROUP BY = Function names
3. Orders = Table Name
4. Sales, segment = Existing column name

• In the Power Bi report view, select the table visual and the card visual. In the table visually drag-drop the sales, segment, and products fields.
• In the same way, in the card visual drag-drop, the created measure value in it displays the group columns and the count values.

In the below screenshot, you can see that the new card visually displays the count values based on the group segment field.

This is how to group the columns and count the values using a distinct count function in Power BI.

Power BI Group by column in table

Let us see how we can group the column values and display them in the Power BI table visual.

By default, the table visual values group the values and display the sales count. For example, in the below screenshot, you can see that the table visually groups the segment values and displays the total sales value.

In this example, we will see how we can group the values in Power BI visual with different categories.

• Load the data using the get data option, once the data has been loaded. select the table visually and also select a slicer visual from the visualizations.
• In the table visually drag-drop the Segment sales and category fields. In the slicer visual add category fields as highlighted below:

• Now to group the column values in the table visual, select any of the categories from the slicer visual it filters and groups the value and display them in the table visual.
• In the below screenshot, you can see I have grouped the furniture category so it groups segment field data value and displays the result in table visual.

Similarly, I have selected the Technology category which also filters and displays the grouped data value in table visual.

This is how to group the column values and display them in the table visual.

Power BI Group by column and concatenate values

Now we will see how we can group by column and concatenate values in Power BI.

In this example, we will concatenate values with the grouped values using the Power Bi Concatenate function in Power BI.

Open the Power Bi Data and load the data using the get data option. Then select the New Table option and apply the below-mentioned formula.

ConcatenatesTable = SUMMARIZE(Orders,Orders[Category (groups)],Orders[Category],"Concatenates",CONCATENATEX(VALUES(Orders[Segment]),Orders[Segment],","))

Where,

1. ConcatenatesTable = New Table Name
2. SUMMARIZE& CONCATENATE = Function names
3. Orders = Table Name
4. Category, segment = Existing column names

In the below screenshot, you can see that the new table concatenates the value and displays the result based on the condition applied.

This is how to group by column and concatenate values in Power BI.

Power BI Group by column in visual

Here we will see how we can Group by column in visual in Power BI.

In this example, we will use the slicer visual and the matrix visual, based on the slicer selection values it groups and displays the result in matrix visual in Power BI.

• Once the data has been loaded. select the matrix visually and also select a slicer visual from the visualizations.
• In the matrix visually drag-drop the category, subcategory, and profit fields. In the slicer visual add category fields as highlighted below:

• Now to group the column values in the matrix visual, select any of the states from the slicer visual it filters and groups the data by category for the selected state profit value and displays them in the matrix visual.
• In the below screenshot, you can see it groups the data and filters the value for the state of Florida.

Similarly, I have selected the state of Georgia which also filters and displays the grouped data value in matrix visual.

This is how to Group by column in visual in Power BI.

Power BI group by column from another table

Let us see how we can group columns from another table in Power BI

In this example, we will find the average value of sales by grouping the Region column in the data source.

Table :1

Table:2

• Once the data has been loaded into the Power Bi desktop. Make sure both the tables have a relationship in the model view as shown below:

• Select the New Measure option under the modeling tab. And then apply the below-mentioned DAX formula to group the values and display the average sales value.

Groupby Table = CALCULATE(AVERAGE(Orders[Sales]), GROUPBY(Orders,People[Region]))

where,

1. Groupby Table = Newly created measure
2. AVERAGE = Function names
3. Orders, People = Existing Table Names
4. Region = Existing column name

• In the Power Bi report view, select the table visual and the card visual. In the table visually drag-drop the sales, segment, and products fields.
• In the same way, in the card visual drag-drop, the created measure value in it displays the group columns and the average sale values.
• In the below screenshot, you can see that the new card visually groups and displays the average sales value.

This is how to group columns from another table in Power BI.

Additionally, you may like some more Power BI tutorials:

• Power BI Bar Chart Conditional Formatting Multiple Values
• Power BI Conditional Formatting Based On Field Value
• Power BI Slicer Multiple Selection
• Power BI Union Two Tables [With Examples]
• Power BI Conditional Formatting Positive and Negative Numbers

This Power BI tutorial explains how to group by column and display the values using group by function. Additionally, we also covered the below-mentioned topics:

• Power BI Group by Dax
• Power BI Group by column and sum
• Power BI Group by column and count
• Power BI Group by column in table
• Power BI Group by column and concatenate values
• Power BI Group by column in visual
• Power BI group by column from another table