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.

Power BI Group by Value example
Power BI Group by Value Example
  • 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.
Example of Power BI Group by Value
Example of Power BI Group by Value
  • 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.
Power BI Group by Column
Power BI Group by Column
  • 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.
Power BI Group by Column example
Power BI Group by Column example

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
See also  Stacked Bar Chart in Power BI | Stacked Column Chart in Power BI

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.

Power BI Group by Value
Power BI Group by Value

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.
Power BI Group by column and count
Power BI Group by column and count

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

Power BI Group by column and count example
Power BI Group by column and count example

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.

Power BI Group by column in table
Power BI Group by column in table

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:
Power BI Group by column in table visual
Power BI Group by column in a table visual
  • 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.
Power BI Group by column in table example
Power BI Group by column in the table example

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

Example of Power BI Group by column in table visual
Example of Power BI Group by column in table visual

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

See also  How to Convert Text to Number in Power BI

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.

Power BI Group by column and concatenate values
Power BI Group by column and concatenate values

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:
Power BI Group by column in visual
Power BI Group by column in visual
  • 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.
Power BI Group by column in visual example
Power BI Group by column in the visual example

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

Example of Power BI Group by column in visual
Example of Power BI Group by column in visual

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

See also  How to Create Table 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

Power BI Group by Value example
Power BI Group by Value Example

Table:2

Power BI group by column from another table
Power BI group by column from another table
  • 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:
Power BI group by column from another table example
Power BI group by column from another table example
  • 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.
Example of Power BI group by column from another table
Example of Power BI group by column from another table

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

Additionally, you may like some more Power BI tutorials:

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
>