Power BI Group By [With 51 real examples]

In this Power BI Tutorial, we will discuss, how to use the Power BI group by with a few real examples.

• Power BI Group By
• Power BI Group By measure
• Power BI Group By month
• Power BI Group By count
• Power BI Group By sum
• Power BI Group By month and year
• Power BI Group By in table visual
• Power BI Group By multiple columns
• Power BI Group By max date
• Power BI Group By all rows
• Power BI Group Rows
• Power BI Group By average
• Power BI Group By and count
• Power BI Group By and sum
• Power BI Group By and concatenate
• Power BI Group By Hour
• Power BI Group By Count
• Power BI Group By and count
• Power BI Group By adding column
• Power BI Group By category
• Power BI Group By counting distinct column
• Power BI Group By date
• Power BI Group By date range
• Power BI Group By day
• Power BI Group By the day of the week
• Power BI Group By duplicates
• Power BI Group By the first date
• Power BI Group By first value
• Power BI Group By financial year
• Power BI graph Group By month and year
• Power BI graph Group By week
• Power BI Group By hour
• Power BI Group By latest date
• Power BI Group By last value
• Power BI Group By max value
• Power BI Group By number range
• Power BI Group By top n
• Power BI Group By percentage
• Power BI Group By rank
• Power BI Group By summarize
• Power BI Group By standard deviation
• Power BI Group By sort

Power BI Group By

To group the data in Power BI, we can use the GroupBy Dax function and group by feature in Power Query.

Power BI Group By function

You can use the GROUP BY DAX function in Power BI to group-specific dimensions in your data and create tables according to the elements (physical or virtual) in your data model.

The syntax of Group By function:

GROUPBY (<Table> [, <GroupBy_ColumnName> [, <GroupBy_ColumnName> [, …]]] [, <Name>, <Expression> [, <Name>, <Expression> [, …]]])

Power BI Group By query Editor

Alternatively, in Power BI we can use PowerQuery’s GroupBy feature. Helps you easily generate valuable data for complex problems. Grouping in Power Query allows you to group values ​​from different rows into a single value.

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 types of columns. 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 that 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 it 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.

This is how to group by in Power Bi desktop without using 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

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 in the below screenshot. Then the chat will appear with the group by Month and Year.

This is how to use Group By month and year in Microsoft 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.

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

This is an example of Power BI Group By Hour.

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 gadgets 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 the 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.

This is how to use the Power BI group by count.

Power BI Group By measure

Here we will see an example of Power BI GroupBy measure using DAX.

For example, we will use the below table to calculate the Distinct count of brands based on Country using DAX.

• In Power BI Desktop, create a new measure to calculate the distinct count of brands based on the country using the below formula:

Unique Brand based on country = CALCULATE(DISTINCTCOUNT('Table'[Brand]), GROUPBY('Table','Table'[Country]))

• Now, create a matrix visual, in the Rows field -> Country and brand, and in Value field -> Value and Unique Brand based on country.
• Then expand to each country to see the brand they have, and the measure will show you the Distinct Brand for each country.

This is an example of Power BI Group By measure.

Power BI Group By month

Here we will see how to group the sales by month using the GroupBy DAX function in Power BI.

For this example, we will use the financial table, we will create a table that will group the sales based on the Month using DAX GROUPBY ().

• In Power BI Desktop, create a Calculated New Table, and in the Formula box, write the below formula:

Sales group by month = GROUPBY(Table1,Table1[Month name], "Sales based on Month", SUMX(CURRENTGROUP(), SUM(Table1[ Sales])))

• Now you can see the table contains total sales for each month in Power BI.

This is how to use Group By month in Power BI.

Power BI Group By count

Here we will see an example of a Power BI Group BY count using Power Query.

For this example, we will use the Financial table, we will count the product based on the product category using Power Query.

In the Power Query editor, select the Product column -> Home tab and select the Group by icon from the ribbon.

• Now Group BY window will open, select the Basic option -> provide the new Column name -> select the operation as Count rows. Click on OK.

• Now you can see the original table get replaced by the Group by table using Power Query in Power BI, and replaced table contains products grouped by count.

This is an example of Power BI Group By count.

Power BI Group By sum

Here we will see an example of Power BI Group by the sum using Power Query

For example, we will use the below table, to calculate the total spending based on country.

• In the Power Query editor, go to the Home tab -> click on Group By from the ribbon.
• The Group By window will open, select the Basic option, -> then select the column as Country -> then provide the New column Name, ->select the Operation as Sum -> select the column as Spend. Click on Ok.

• Now you can see the original table is replaced by Group By table having Country and Total spend columns.

This is how to use the Power BI Group By sum.

Power BI Group By month and year

Here we will see an example of Power BI Group by Month and year using Power Query.

For example, we will use the Financial table, to group the Total sales by Month and year columns using Power Query.

• In Power Query Editor, go to the Home tab -> click on Group By icon from the ribbon.
• Then the Group By window will open, select the Advanced option, -> then select the Month column -> click on Add grouping -> select the Year column.
• Next, provide the New column Name as Total sales, -> select the Operation as Sum, -> select the column as Sales. Then click on Ok.

• Now, you can see the new table is replaced the original table having Total sales column group by Month and Year.

This is an example of Power BI Group By month and year.

Power BI Group By in table visual

Here we will see an example of Group By in the table visual in Power BI.

For this example, we will use the below table. Instead of using a table visualization, we can use the Matrix visual, which will group the data. Here we will group the Brand based on Country.

• In Power BI, create a Matrix visual, then in the Rows field drag the country and brand from the field pane.
• In the Values field, drag the Spend column from the visualization pane.

This is an example of Group By in table visual in Power BI.

Power BI Group By multiple columns

Here we will see an example of a Power BI group by multiple columns using Power Query.

For example, we will use the below table and group the spending based on country and brands.

• In Power Query Editor, go to the Home tab -> click on Group By icon from the ribbon.
• Then the Group By window will open, select the Advanced options, -> select the Country column -> click on Add grouping -> the select Brand column.
• Next, provide the new column name, select the Operation as Sum and select the Column as Spend. Then click on OK.

• Now, you can see the original table getting replaced with Group By table, having three columns i.e Country, Brand, and Total spend.

This is an example of Power BI Group By multiple columns.

Power BI Group By max date

Here we will see an example of the Power BI Group By Max Date using Power Query.

For this example, we will use the Financial table, for each Product group we will get the Max date using Power Query.

• In Power Query Editor, go to the Home tab -> select the Group By feature from the ribbon.
• Now, Group By window will open, select the Basic option -> Then select the Product column.
• Next, provide the New column name, -> then select the Operation as Max, -> select the Date in the Column box.

Now you can see the table replaced with the Grouped table, having product names with the max date.

This is an example of Power BI Group By max date.

Power BI Group By all rows

Here we will see an example of Power BI Group By all rows using Power Query.

For this example, we will use the Financial table and we will group all rows in a table. When we select the All rows option as an operation in the Power Query Group By feature, that will contain all the rows for the columns of the inner table, you grouped by.

• In Power Query Editor, go to the Home tab -> select the Group By feature.
• The Group By window will open, then click on the Advanced option -> select the Product column-> click on Add grouping -> select the Profit column.
• Next, provide the column name, then select the operation as Sum, and column as Profit.
• Then click on Add Aggregation, and provide the column name, select the operation as ‘All rows’. Click on OK.

Now, you can see the table get replaced with a Group table. And the Country contains all rows, so expand the column to get all rows. For this click on the double arrow icon -> Expand option, then select the column of all rows you want to show from the internal table. Click on Ok.

• Next, you can see the Product and profit grouped by all rows using Power Query.

This is an example of Power BI Group By all rows.

Power BI Group By average

Here we will see an example of Power BI Group By average using Power Query.

For example, we will use the below table, and we will calculate the average spending for each category.

• In the Power Query editor, go to the Home tab -> select Group By icon from the ribbon.
• Now Group By window will open, so select the Basic option -> select the Brand column.
• Then provide the new column name -> select the Operation as Average, -> Column as Spend. Click on Ok.

• Now you can see the Average spend based on each brand in Power Query Editor.

This is an example of Power BI Group By average.

Power BI Group By and sum

Here we will see an example of Power BI Group BY and sum using Dax.

For example, we will use the below table, and we will group the total spend based on each country.

• In Power BI, create a new table, and then write the below formula in the Formula box, to group the total spend based on each country.

Table 2 = GROUPBY('Table','Table'[Country], "Total spend", SUMX(CURRENTGROUP(),'Table'[Spend]))

• Next, you can see a new table get created with total spend grouped by country.

This is an example of Power BI Group By and sum.

Read Line Chart in Power BI

Power BI Group By and concatenate

Here we will see an example of Power BI Group BY and concatenate using Power Query.

For example, we will use the below table, we will group the Total spend and brand based on the Country and Advertiser column using Power Query. And we will concatenate the Brand column i.e. C1 -> B1, B2, B3.

• In Power Query Editor, go to the Home tab -> click on Group By icon from the ribbon.
• Now Group By window will open, then click on the Advanced options -> select the Country column -> click on Add grouping -> select the Advertiser column.
• Next provide the New column name, -> select the operation as Sum -> select the column as Brand.
• Then click on Add aggregation -> then provide the new column name -> select the operation as Sum -> select the column name as Spend. Click on Ok.

Now you can notice that the brand column contains an error, this is because the power query performs a sum operation for text values and it won’t concatenate the text values.

To concatenate the brand column, we need to change the aggregate part of the code. The Power Query use List. sum to aggregate the Spend column and Brand column. But List. sum () only aggregates the Number type, so the Brand column won’t be aggregated. So to concatenate the Brand, we will use the Text. combine() in the code, which will concatenate the Brand column value based on the group.

Change the below-highlighted part of the code in the formula box, with the below code:

Text.Combine([Brand],",")

Now you can see the brand values are concatenated based on the Country and Advertiser.

This is an example of Power BI group by and concatenate.

Power BI Group By and count

Here we will see an example of Power BI Group By and count using Dax.

For example, we will use the table below to count the number of brands based on the Country column.

• In Power BI Desktop, go to the Modelling tab -> click on Create New table.
• To create a new table in the formula box -> write the below formula:

Table 4 = GROUPBY('Table','Table'[Country], "Total Brand", COUNTX( CURRENTGROUP(),'Table'[Brand]))

• Once you have created you can see a new table get created in Power BI, with 2 columns, containing a country name column, number of brands based on country

This is an example of Power BI Group By and count.

Power BI Group By adding a column

Here we will see an example of Power BI Group By adding a column using DAX.

For example, we will use the below table, and we will group the Brand column based on the Country by concatenating it with ‘,’, if that brand spends is more than 15. Next, we will group the Advertiser based on the Country by concatenating with ‘,’, if that spending is more than 40.

• In Power BI Desktop, go to the Data Modeling -> click on New table from the ribbon.
• Then write the below formula in the formula box:

Attributes = 
ADDCOLUMNS (
    GROUPBY ( 'Table', 'Table'[Country] ),
    "BrandGroup", CALCULATE (
        CONCATENATEX (
            VALUES ( 'Table'[Brand] ),
            'Table'[Brand],
            ",",
            'Table'[Brand]
        ),
        'Table'[Spend] >= 15
    ),
    "AdvertiserGroup", CALCULATE (
        CONCATENATEX (
            VALUES ( 'Table'[Advertiser] ),
            'Table'[Advertiser] ,
            ",",
            'Table'[Advertiser] 
        ),
       'Table'[Spend] >= 40
    )
)

• Now you can see the table getting created with three columns, a country column, a brand group column, and an Advertisergroup column.

This is an example of Power BI Group By add column.

Power BI Group By category

Here we will see an example of the Power BI Group BY category using Dax.

For example, we will use the below table and we will calculate the total spend for each group of advertisers.

• In Power BI Desktop, go to the Modelling tab -> click on New Measure from the ribbon.
• Then write the below DAX formula in the formula box:

Spend by Advertiser = CALCULATE(SUM('Table'[Spend]),ALLEXCEPT('Table','Table'[Advertiser]))

• Now, from the Visualization pane select the table visual -> then drag and drop the Advertiser column, and Measure from the Field pane.
• Now you can see the total spend by the Brand in the table visual in Power BI.

This is an example of the Power BI Group By category.

Power BI Group By counting distinct column

Here we will see an example of Power BI Group By counting distinct columns using Power Query.

For example, we will use the below table and we will count the distinct brand and show the total spend.

• In Power Query Editor, go to the Home tab -> select the Group By column from the ribbon.
• The Group By window will open, select the Advanced options. Then select the Brand column for grouping. Next, provide the column name and select the operation as Distinct Count rows. Then click on Add aggregation -> provide the column name, select the operation as count Distinct rows, and select the column as spend. Click on OK.

Now, you can see the table having the distinct count rows column for the brand.

This is an example of Power BI Group By counting distinct column

Power BI Group By date

Here we will see an example of Power BI Group By date using Power Query.

For example, we will use the below table we will group the date column and sum the stock value using Power Query.

• In Power Query Editor, go to the Home tab -> select Group By icon from the ribbon.
• Now, the group By window will open, so select the Basic option, -> select the Date column. And then provide the column name, select the operation as Sum, and select the column as Stock.

So you can see the total stock value based on the grouped date column using Power Query.

This is an example of Power BI Group By date.

Power BI Group By date range

Here we will see an example of a Power BI group by date range using DAX.

For example, we will use the below table to calculate the total stock based on the date range using DAX.

• In Power Bi Desktop, go to the Modeling tab -> click on the new column to create the Date range.
• Then write the below DAX formula in the Formula box:

Datebucket = SWITCH(TRUE(), AND(DATEDIFF(Stock[Date], TODAY(),DAY)>=0, DATEDIFF(Stock[Date].[Date],TODAY(),DAY)<=15), "0-15 days",
AND(DATEDIFF(Stock[Date], TODAY(),DAY)>=16, DATEDIFF(Stock[Date].[Date],TODAY(),DAY)<=30), "16-30 days",
AND(DATEDIFF(Stock[Date], TODAY(),DAY)>=31, DATEDIFF(Stock[Date].[Date],TODAY(),DAY)<=59), "31-59 days",
DATEDIFF(Stock[Date].[Date], TODAY(),DAY)>=60, "60 days")

• Next click on the new table in the Modelling tab, to group the total stock based on the date range.

• Then write the below formula in the formula box, and you can see the total stock gets created based on the date range.

Date range = GROUPBY(Stock,Stock[Aging bucket], "Total Stock",SUMX(CURRENTGROUP(),Stock[Stock]))

This is an example of Power BI Group By date range.

Power BI Group By day

Here we will see an example of Power BI Group BY day example using Power Query.

For this example, we will use the Financial table, to calculate the total sales based on Group By day.

• In Power Query Editor -> go to the Home tab ->click on Group By icon from the ribbon.
• Then Group By window will open -> select the Basic option -> then select the Day column.
• Next, provide the column name -> select the Operation as Sum -> select the sales column. Click on Ok

Now you can see the Day column is grouped with day number and based on that Total sales get calculated.

This is an example of Power BI Group By day.

Power BI Group By the day of the week

Here we will see an example of Power BI Group BY day of the Week using Power Query.

For example, we will use the below financial table, and we will group the Day of the week and calculate the total sales.

• In Power Query Editor, go to Howe tab -> select Group By icon from the ribbon.
• The Group By window will open, select the Basic option -> then select the Day of the week
• Next provide the column name, select Operation as Sum, and then select the column as Sales.

Now you can see the table having groups by day of the week with total sales.

This is an example of Power BI Group By the day of the week.

Power BI Group By duplicates

Here we will see an example of Power BI Group BY duplicates using Power Query

For example, we will use the below table to count the number of duplicates from Country, Brand, and Advertiser, by grouping them using Power Query.

• In Power Query Editor, go to the Home tab -> click on Group By icon from the ribbon.
• Then select the Advanced option, select the column for grouping, then click on Add grouping select the other two-column.
• Next, provide the column name and select the operation as Count. Click on OK.

Now you can see a table get created with the count of duplicates present in the grouping.

This is an example of Power BI Group By Duplicates.

Power BI Group By the first date

Here we will see an example of Power BI Group By the first date using DAX.

For example, we will use the below table, to get the first/earliest date for each customer id, who purchased item A and return 1 on a new column.

In Power BI Desktop, go to the Modelling tab -> select New column, and write below the Dax formula in the formula box:

Column_Want = 
IF (
    'Table (2)'[Date]
        = CALCULATE (
            MIN ( 'Table (2)'[Date] ),
            FILTER (
                ALLEXCEPT ('Table (2)', 'Table (2)'[Customer id], 'Table (2)'[item id] ),
                'Table (2)'[item id]  = "A"
            )
        ),
    1
)

• Now, you can see the column gets added with value 1, to identify the earliest date for customer id purchased item A.

This is an example of Power BI Group By the first date.

Power BI Group By first value

Here we will see an example of Power BI Group By first value using Dax.

For example, we will use the below table to get the first value for each id using DAX.

• In Power BI, go to the Modelling tab -> click on the New column from the ribbon, to calculate the first date, and write the below formula in the formula box:

EntryDate = CALCULATE(Min(Stock[Date]),ALLEXCEPT(Stock,Stock[Id]))

• Next click on the New column from the Modeling tab to get the first stock value of each id, for this write the below formula in the formula box:

InitialStock = MAXX(FILTER(ALL(Stock),Stock[Id] = EARLIER(Stock[Id] ) && [Date] = EARLIER([EntryDate])),[Stock])

• So you can see the first stock value of each id in the New column.

This is an example of Power BI Group By first value.

Power BI Group By financial year

Here we will see an example of Power BI Group By financial year using Power Query.

For this example, we will use the Financial table, to get the total sales for each financial year.

• In Power Query Editor, go to the Home tab -> select the Group By icon from the ribbon.
• Now the Group By window will open, select the Basic option, and select the Year column for grouping.
• Next, provide the column Name, select the operation as Sum, and select the Sales column.

Now you can see the table having years with total sales columns in Power Query.

This is an example of Power BI Group By Financial year.

Power BI graph Group By month and year

Here we will see an example of a Power BI graph group by month and year using Power Query.

For this example, we will use the Financial table to calculate the total sales and group By month and year.

• In Power Query, go to the home tab -> select Group By icon from the ribbon.
• Group By window will open, select the Advanced option -> then select the year column and click on Add grouping -> select the Month column.
• Next, provide the new column name -> select the operation as Sum, and then select the Sales column. Click on OK.

Now you can see the new table having total sales which are grouped for Year and month.

This is an example Power BI graph Group By month and year.

Power BI graph Group By week

Here we will see an example of a Power BI graph Group By week using Power Query.

For example, we will use the Financial table, to group the dates by week using Power BI.

• In Power BI Desktop, select the Stacked column chart -> in the axis drag the Date column ( Year and Month), and add the date column one more time to the axis -> right-click and select Date. In the value field, add the Sales column from the field pane.
• Then right-click on Date (Second date in Axis) -> click on New Group.

• Now the Groups window will open, -> Name it as ‘Week’ -> Group type is Bin -> Set the Bin size as 7 days. Click on Ok.

• Now in the Stacked column chart click on the drill down icon to drill down to the Week level.
• In The Filter, pane Expand the Week -> Filter type is Basic Filtering -> select any date that is grouped by Week.
• Next to that, you can see the number of days get group, in this case, it is 7.

This is an example of a Power BI graph Group By week.

Power BI Group By hour

Here we will see an example of a Power BI group By hour using Power Query.

For example, we will use the Financial table and calculate the total sales and group by the hour using Power Query.

• In Power Query Editor, go to the Home tab -> select Group By icon from the ribbon.
• The Group By window will open, select the Basic option, then select the column as Hour.
• Next, provide the new column name, then select the operation as Sales and select the Sales column from the ribbon. Click on Ok.

• Next, in the table, you can see the total sales are grouped by the hour using Power Query.

This is an example of Power BI Group By the hour.

Power BI Group By latest date

Here we will see an example of Power Bi Group By latest date or the last Date using a power query.

For example, we will use the table below and group the latest date based on item id using Power Query.

• In Power Query Editor, go to the Home tab -> click on the Group By icon from the ribbon.
• The Group By window will open, so select the Basic option and select the item id column.
• Then provide the column name, -> select operation as max, and select the column as Date.

Now you can see the table’s latest date is grouped by item id using Power Query.

This is an example of Power BI Group By latest date.

Power BI Group By rank

Here we will see an example of Power Bi Group By rank using DAX.

For this example, we will use the below table to rank the value based on Group By Article name.

• In Power BI Desktop, go to the Modeling Tab -> create a New column from the ribbon.
• Then write the below formula in the formula box:

Rank Column = RANKX(FILTER('Table (3)','Table (3)'[Article name]=EARLIER('Table (3)'[Article name])),'Table (3)'[Value],,Asc,Dense)

• Now you can see the Rank column is created, and rank the value for each category.

This is an example of Power BI Group By rank.

Power BI Group By last value

Here we will see an example of Power BI Group BY last value using DAX.

For example, we will use the below table to get the last value for each group of Articles using DAX.

• In Power Bi Desktop, go to the Modelling tab -> click on New measure, then write the below formula in the Formula box.

Last Value = 
CALCULATE (
    MAX ( 'Table (3)'[Value]),
    FILTER (
        ALLEXCEPT ('Table (3)', 'Table (3)'[Article name] ),
       'Table (3)'[Rank Column] = MAX ( 'Table (3)'[Rank Column] )
    )
)

• Next, create a table visual, and in values add the Article name column and Last value measure. Then you can see the table visual having the last value for each category.

This is an example of Power BI Group By last value.

Power BI Group By max value

Here we will see an example of a Power BI group by max value using Power Query.

For example, we will use the below table to get the Max value based on the Grouped Article using Power Query.

• In Power Query Editor, go to the Home tab -> select the Group By icon from the ribbon.
• The Group By window will open, select the basic option, and select the Article name column.
• Then provide the column name -> select the operation as Max, and then select the Value column. Click on Ok.

Now you can see the table contains Max value for each Article.

This is an example of Power BI Group By max value.

Power BI Group By number range

Here we will see an example of a Power BI group by number range using Dax.

For example, we will use the below table to group the Country column based on the number range using Dax.

In Power Bi Desktop, first, we will create a number range, so go to the Modeling tab -> click on the New column from the ribbon, then write the below formula in the Formula box.

Group Name = 
SWITCH (
    TRUE (),
    'Table'[Spend] >= 0
        && 'Table'[Spend] <= 30, "0-30",
    'Table'[Spend] >= 30
        && 'Table'[Spend] <= 100, "40-100",
    "other"
)

Next, go to the Modelling tab -> select the New table, and then write the below DAX formula in the formula box:

Table 3 = GROUPBY('Table','Table'[Country],'Table'[Group Name])

• Now you can see the table getting created, grouped the country column-based o n the number range.

This is an example of Power BI Group By number range.

Power BI Group By the top

Here we will see an example of Power BI Group By the top using DAX.

For example, we will use the below table to get the sum of the top 5 values for each category.

• In Power BI Desktop, go to the Modeling tab -> click on the New table from the ribbon.
• Then write the below DAX formula in the Formula box:

NewTable = TOPN(5,SUMMARIZE('Table 6','Table 6'[Category],"TotalValue",SUM('Table 6'[Value]) ))

• Now you can see the table having the sum of the top 5 values of each category.

This is an example of Power BI Group By the top.

Power BI Group By percentage

Here we will see an example of Power BI Group By percentage using DAX.

For example, we will use the below table to calculate the percentage of the total for each group.

In Power BI Desktop, in the Home tab -> click on the New measure from the ribbon. Then write the below DAX formula in the Formula box:

MeasureB = (CALCULATE(SUM('Table 6'[Value]),FILTER('Table 6','Table 6'[Category]=MAX('Table 6'[Category])&&'Table 6'[Month]=MAX('Table 6'[Month]))))/CALCULATE(SUM('Table 6'[Value]),FILTER(ALL('Table 6'),'Table 6'[Month]=MAX('Table 6'[Month])))

• Then select the Measure, and change the format to Percentage from the ribbon.

• Now, in the Report view, select the clustered column chart from the visualization pane.
• Then drag the Category -> Axis, Month -> Legend, and Values -> Measure B from the field pane to the field.
• Next click on the Format section in the Visualization pane -> then turn on the data label.

This is an example of Power BI Group By percentage.

Power BI Group By summarize

Here we will see an example of Power BI Group By summarizing using DAX.

For example, we will use the below table to calculate the average value and total counts using Dax.

• In Power BI Desktop, go to the Modelling tab -> click on the New table from the ribbon.
• Then write the below formula in the Formula box:

Totals = SUMMARIZE('Table 6',
   'Table 6'[Category],
   "Average value per category",
AVERAGE('Table 6'[Value]),
   "Number of them",
COUNT('Table 6'[Value]))

• Now the table gets created with the category, the average value per category, and the number of categories.

This is an example of Power BI Group By summarize.

Power BI Group By standard deviation

Here we will see an example of Power BI Group By standard deviation using Power Query.

For example, we will use the below table and group the table by Article name, and calculate the Standard Deviation based on grouped Article name.

• In Power Query Editor, go to the Home tab -> click on the Group By icon from the ribbon.
• Now the Group By window will open, select the Basic option, then select the Column Name.
• Next, provide the New column name, then select the Operation as Sum, and then select the column as Value.

Now the new table gets created with 2 columns having the Article name and Total value based on the group of each article. To get the Standard deviation, we need to change the List. Sum to List.StandardDeviation. So change the below code which is bolded.

This is an example of Power BI Group By standard deviation.

Power BI Group By sort

Here we will see an example of Power BI Group BY sort.

For example, we will use the below table to sort the Type column in Power BI.

• In Power BI Desktop, select the Table visual from the visualization pane.
• Then drag the Type and Qty field from the field pane.
• Next click on the More icon on the Top of the visual -> clicks on the Sort By -> select the Type field from the option.

Now you can see the type column is sorted in ascending order.

This is an example of Power BI Group By sort.

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

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
• Power BI Error: This content isn’t available
• Subtraction in Power bi using DAX
• Power BI Measure Sum and Subtract Example
• Power BI Error: This content isn’t available
• Power bi shared dataset permissions management
• Power BI nan error (Not a number) while dividing by Zero

Here, in this tutorial, we learned how to use group by in Power BI with a few examples like below:

1. Power BI Group By
2. Power BI Group By measure
3. Power BI Group By month
4. Power BI Group By count
5. Power BI Group By sum
6. Power BI Group By month and year
7. Power BI Group By in table visual
8. Power BI Group By multiple columns
9. Power BI Group By max date
10. Power BI Group By all rows
11. Power BI Group Rows
12. Power BI Group By average
13. Power BI Group By and count
14. Power BI Group By and sum
15. Power BI Group By and concatenate
16. Power BI Group By Hour
17. Power BI Group By Count
18. Power BI Group By and count
19. Power BI Group By adding column
20. Power BI Group By category
21. Power BI Group By counting distinct column
22. Power BI Group By date
23. Power BI Group By date range
24. Power BI Group By day
25. Power BI Group By the day of the week
26. Power BI Group By duplicates
27. Power BI Group By the first date
28. Power BI Group By first value
29. Power BI Group By financial year
30. Power BI graph Group By month and year
31. Power BI graph Group By week
32. Power BI Group By hour
33. Power BI Group By latest date
34. Power BI Group By last value
35. Power BI Group By max value
36. Power BI Group By number range
37. Power BI Group By top n
38. Power BI Group By percentage
39. Power BI Group By rank
40. Power BI Group By summarize
41. Power BI Group By standard deviation
42. Power BI Group By sort