Power BI Group By Examples

In this Microsoft Power BI Tutorial, We will see various examples on how to use Power BI Group by. We will also see the Power BI Group Rows, Grouping in Power BI Desktop, Grouping in Power BI Desktop without using Power BI DAX.

Also, we will see what is Group By month in Power BI, Power BI Group By month and year, Power BI Group By Hour and Power BI Group By Count.

If you are new to Microsoft Power BI, then using this below article, you can learn the Power BI from the beginning: Getting started with Microsoft Power BI Tool

Power BI Group Rows

The Group By option helps to group the values in multiple rows into a single value in the Query Editor. You can group by values like total revenues, count of employees, count of products, etc.

To create Group By in Power BI, there are two different approaches are present as:

  • By using the Home tab:

The first approach to create Group By in Power BI, Go to the Home tab and then click on Group By option which is present in the ribbon section as shown below.

Group by in Power BI
  • By using the Transform tab:

The second approach to create Group By in Power BI, Go to the Transform tab (inside the Edit queries button in Power BI Desktop) and then click on Group By option which is present in the ribbon section as shown below.

Group by in Power BI Edit queries

Once you will click on Group By option, it will open the following window where you have to enter some required values as:

  • Group by: Choose any column which you want to be grouped. By default, the Query Editor selected any one column, but you can change it to be any column from the drop-down.
  • New column name: Specify the name of the new column which will display in the Power BI query editor.
  • Operation: Provide some query operations like sum, Count rows, etc, as per your requirement.
  • Add grouping: When you will select the Advanced option, then only you can view this option. By using this option, you can perform the Group by actions on multiple columns.
  • Add aggregation: When you will select the Advanced option, then only you can view this option. By using this option, Query Editor creates a new column (based on your selections in this window) that operates on multiple columns.

Once you will enter all the field values, Just click on OK as the below screenshot.

Group by Query Editor in Power BI

Now in the below screen, you can see the Group By value is displaying in another new column as Count.

power bi group visuals

Grouping in Power BI Desktop

Here, I will show you how you can group a bundle of data in the Power BI Desktop.

Suppose you want to view some of the data should be in the same color or you want to highlight some data for your requirement purpose, then you can use the Group data option as explained below.

To do the Group data in Power BI Report, you have to follow the below steps:

Step-1:

In this example, I have taken the Sales Report table and Stacked column chart under the Visualizations section.

One of the most important things you should know is, Before starting to do anything with the table, You have to format all the data type of column. If you want to know the Power BI Data types, then click on this link: Power BI Data type.

In this chart, I have used the Country in Axis field and Bill No. in the Value field as you can see in the below screenshot.

Grouping in Power BI Desktop

Step-2:

Now I want to group the data by country. So I have selected some data (By country) that I want to view in the group by. You can select one or more data by using Ctrl+click.

Once the data selection is over, just right-click on any data and select the Group data option as shown below.

group in power bi desktop

Step-3:

Now you can see there will be a group column which is created automatically in the right side Fields section. Just add that grouped column in the Legend section.

As in this example, I have grouped by Country, So it appeared as [Country (groups)]. As I have added this grouped column in the Legend section, that’s why the chart is appearing with group data (in different colors) like the below screenshot.

power bi group data in table

Edit the Group data in Power BI Desktop

In Power BI Desktop, If you want to do any changes or modify the existing group data, then you can do the changes by using the Edit groups option.

Just click on the down arrow of the grouped column that you want to modify and click on Edit groups. Also, you can directly right-click on the grouped column.

power bi group rows in table

Once you will click on the Edit groups, then the below window will appear where you can modify the Group column data.

Similarly, If you want to ungroup the data, then simply click on the Ungroup button and then click on OK as shown below.

how to create a group in power bi desktop

Grouping in Power BI Desktop without using DAX

In this below example, I will show you how we can use grouping in Power BI Desktop without using the DAX Function.

In the Power BI Desktop, I have taken a table named Sales Details. It has two columns as:

  • Gadgets (Single line of text data type)
  • Total Sales (Whole number data type)

In this example, I want to group some gadgets like Heavy gadgets and Light Gadgets. As some gadgets like AC, Desktop, Refrigerator, etc are like heavy gadgets, So I want to make a group as Heavy Gadgets.

Similarly, Mobile, Tablet, iPod, etc are under the Light gadgets, So I want to make another group name as Light Gadgets.

To create a new group, right-click on the table name or any column name and select New group.

Grouping in Power BI Desktop without using DAX

When you will click on New group, then the below window will appear. As I want to make a group with heavy gadgets, So I have selected more than one heavy gadgets with Ctrl+click. Click on Group as like below.

without using DAX Grouping in Power BI Desktop

Just rename the new group name if you want. Here, I renamed the group name as Heavy Gadgets by double-clicking on the name bar.

group in power bi desktop without using dax

In the same way, I have selected multiple light gadgets and clicked on the Group button as shown below.

power bi desktop group without using dax

Again renamed the new group as Light Gadgets by double-clicking on the name bar.

group in power bi desktop without use dax

If you want to view the other group (By default) under the Groups and members section, then check the Include Other group option which is present at the right bottom of the page.

how to create Grouping in Power BI Desktop without using DAX

After creating the group in Power BI Desktop, Don’t forget to test the group. To test the new group, I have taken a Matrix chart under the Visualizations section.

Then I added Gadgets (groups) into the Rows field and Total Sales into the Values field as shown below.

Now you can see the gadget group will appear with two different groups as Heavy Gadgets and Light Gadgets as you can see in the below screenshot.

create Grouping in Power BI Desktop without using DAX

To modify or change anything in the Group, You can select the Edit groups by right-clicking on the group name. After that, In the Editor window, you can change the thing as per your need.

how to create group in power bi desktop without using dax

If you want to ungroup the group, then select the group name and click on the Ungroup button.

how to create group in power bi desktop without use dax

After ungrouping the data, the data will display under the Ungrouped values category. Click on OK.

create group in power bi desktop without use dax

Power BI Group By month and year

In this below example, I will show you how you can make a Power BI Group by Month and Year.

For this example, I have a table named Product Sales. In this table, I have a Date column named as Sales Date. As I have formatted the data type of Sales Date as Date/Time, So a date hierarchy is coming automatically in the Sales Date field. The Date hierarchy is having these below:

  • Year
  • Quarter
  • Month
  • Day
power bi group by month and year

Take any chart under the Visualizations section, put the Year in Axis and Total Sales in the Value field. Now the visual will appear with Total Sales by year as below.

group by month and year power bi

Similarly, When you will take the Month in Axis field and Total Sales in the Value field, then the visual will appear with Total Sales by Month as shown below figure.

group by month and year in power bi

To view the Total Sales by Year and Month, Create a new column and put the below formula as:

Year And Month = DATE(YEAR([SalesDate]), Month([SalesDate]), 1)

Where

  • Year and Month= New Column name of the table
  • SalesDate= Date column of the Product Details table

Once you created the new column, just put into the Axis field and Total Sales into the Value field as like the below screenshot. Then the chat will appear with the group by Month and Year.

group by month in power bi

Power BI Group By Hour

If you want to view the report on an hourly basis, then you can make a Group By Hour. For that, create a new column and apply the below formula:

Hour = TIMEVALUE('Product Sales'[SalesDate])

Similarly, If you want to view the report with minute basis, then follow the below formula:

Minute = MINUTE('Product Sales'[SalesDate])

Where

  • Product Sales= Table name
  • Sales Date= Date column of the Product Sales table

Once you have created the Hour or Minute column, Just format the data type of those columns as Time.

To do that, Go to Modelling tab -> Select Time from Data type -> Select Date Time from Format section and then select HH: MM as like below.

power bi group by hour

Now you can use the Hour or Minute column by putting it into the Axis section as shown below.

group by hour in power bi

Power BI Group By Count

By taking a simple example, here I’ll show you how you can use the power Bi Group by count.

For this example, I’ve taken a Product Details table Which has columns like City, Sales Dates, Gadgets, Total Sales, etc.

When I put these columns into the Table chart, then it looks like the below screen. Here I want to count the total number of gadgets.

Suppose, In the below table, It has a Laptop gadget in Australia and as well as in Germany. So the total count of the laptop is two. Similarly, I want to view the total count of all the gadgets that the table has.

power bi group by count

To count each total gadget, follow the below steps.

Step-1:

In Power BI Desktop, Go to the Home tab -> Click on the Edit Queries button from the ribbon -> Select Edit Queries.

power bi group by count distinct

Step-2:

Once you will click on the Edit Queries option, then the below Query Editor window will appear. Go to the Transform tab -> Click on Group By button.

power bi query editor distinct count

Step-3:

In the Group By Page window, Select Advanced option. As I have to count the gadgets, So I have chosen the Gadgets column.

Enter a name for the New column and choose the Count Rows from the Operation drop-down. Click on OK.

power bi distinct count group by

Now in the Query Editor window, you can see the total count of Gadgets those will be appearing under the new column.

distinct count with group by in power bi

Also, You may like the below Power BI articles:

Hence in this Microsoft Power BI Tutorial, We discussed the Power BI Group Rows, Grouping in Power BI Desktop, Grouping in Power BI Desktop without using DAX.

Also, we saw what is Group By month in Power BI, Power BI Group By month and year, Power BI Group By Hour and Power BI Group By Count by taking some examples.

>