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.

• 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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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.

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.

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.

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

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

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

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.

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.

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.

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.

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

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.

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.

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.

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.

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

Also, You may like the below Power BI articles:

• Power BI report using People Picker Field
• Power BI Measure Sum and Subtract Example
• Data Labels in Power BI
• Get Current Month Sales Report using Power BI Measure
• Get Next Month Sales Report in Power BI
• Power BI Error: This content isn’t available
• Subtraction in Power bi using DAX
• Power BI Measure Sum and Subtract Example
• [Video Tutorial] Create Power BI Dashboard step by step tutorial
• Power BI Error: This content isn’t available
• Power bi shared dataset permissions management
• Scheduled Power BI report data refresh
• Power BI nan error (Not a number) while dividing by Zero
• How to change data source in Power Bi

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.