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.

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
power bi group data in table

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.

power bi group data in table
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

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

group by month in power bi
Group By month and year in Power BI

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.

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 Hour

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.

See also  Power BI FREE vs PRO vs PREMIUM

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.

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

power bi distinct count group by
Power BI Group By Count

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
Power BI Group By Count

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.

Power BI Group By measure
Power BI Group By measure
  • 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.
Microsoft Power BI Group By measure
Microsoft Power BI Group By measure

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.
Power BI Group By month
Power BI Group By month

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.

Power BI Group By count
Power BI Group By count
  • Now Group BY window will open, select the Basic option -> provide the new Column name -> select the operation as Count rows. Click on OK.
Power BI Group By count using Power Query
Power BI Group By count using Power Query
  • 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.
Microsoft Power BI Group By count using Power Query
Microsoft Power BI Group By count using Power Query

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.

Power BI Group By sum
Power BI Group By sum
  • 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.
Microsoft Power BI Group By sum
Microsoft Power BI Group By sum
  • Now you can see the original table is replaced by Group By table having Country and Total spend columns.
Microsoft Power BI Group By sum using power query
Microsoft Power BI Group By sum using power query

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.
Power BI Group By month and year
Power BI Group By month and year
  • Now, you can see the new table is replaced the original table having Total sales column group by Month and Year.
Power BI Group By mnth and year using power query
Power BI Group By month and year using power query

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.

Power BI Group By in table visual
Power BI Group By in table visual
  • 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.
Microsoft Power BI Group By in table visual
Microsoft Power BI Group By in table visual

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.

Power BI Group By multiple columns
Power BI Group By multiple columns
  • 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.
Microsoft Power BI Group By multiple columns
Microsoft Power BI Group By multiple columns
  • Now, you can see the original table getting replaced with Group By table, having three columns i.e Country, Brand, and Total spend.
 Group By multiple columns in Microsoft Power BI
Group By multiple columns in Microsoft Power BI

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.
Power BI Group By max date
Power BI Group By max date

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

Power BI Group By max date using Power Query
Power BI Group By max date using Power Query

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.
Power BI Group By all rows
Power BI Group By all rows

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.

Microsoft Power BI Group By all rows
Microsoft Power BI Group By all rows
  • Next, you can see the Product and profit grouped by all rows using Power Query.
Microsoft Power BI Group By all rows using Power Query
Microsoft Power BI Group 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.

Power BI Group By average
Power BI Group By average
  • 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.
Microsft Power BI Group By average
Microsft Power BI Group By average
  • Now you can see the Average spend based on each brand in Power Query Editor.
Microsft Power BI Group By average using Power Query
Microsoft Power BI Group By average using Power Query

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.

Power BI Group By and sum
Power BI Group By and sum
  • 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.
Power BI Group By and sum using DAX
Power BI Group By and sum using DAX

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.

Power BI Group By and concatenate
Power BI Group By and concatenate
  • 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.
Microsoft Power BI Group By and concatenate
Microsoft Power BI Group By and concatenate

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.

 Group By and concatenate in Power BI
Group By and concatenate in Power BI

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.

See also  How to Export Power BI Reports to PDF?

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

Text.Combine([Brand],",")
Group By and concatenate in Microsoft Power BI
Group By and concatenate in Microsoft Power BI

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

Microsoft Power BI Group By and concatenate using Power Query
Microsoft Power BI Group By and concatenate using Power Query

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.

Power BI Group By and count
Power BI Group By and count
  • 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
Power BI Group By and count using DAX
Power BI Group By and count using DAX

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.

Power BI Group By concatenate
Power BI Group By concatenating
  • 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.
Power BI Group By add column
Power BI Group By adding a 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.

Power BI Group By category
Power BI Group By category
  • 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.
Power BI Group By category using DAX
Power BI Group By category using DAX

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.

Power BI Group By count distinct column
Power BI Group By count distinct column
  • 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.
Power BI Group By count distinct column using Power QUery
Power BI Group By count distinct columns using Power Query

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

Microsoft Power BI Group By count distinct column using Power QUery
Microsoft Power BI Group By counting distinct column using Power Query

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.

Power BI Group By date
Power BI Group By date
  • 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.
Microsoft Power BI Group By date
Microsoft Power BI Group By date

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

Microsoft Power BI Group By date using Power Query
Microsoft Power BI Group By date 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.

Power BI Group By date range
Power BI Group By date range
  • 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")
Power BI Group By date range using DAX
Power BI Group By date range using DAX
  • 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]))
Microsoft Power BI Group By date range using DAX
Microsoft Power BI Group By date range using DAX

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
Power BI Group By day
Power BI Group By day

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

Power BI Group By day using Power Query
Power BI Group By day using Power Query

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.
Power BI Group By day of week
Power BI Group By day of week

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

Power BI Group By day of week using Power Query
Power BI Group By the day of the week using Power Query

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.

Power BI Group By duplicates
Power BI Group By duplicates
  • 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.
Microsoft Power BI Group By duplicates
Microsoft Power BI Group By duplicates

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

Microsoft Power BI Group By duplicates using Power Query
Microsoft Power BI Group By duplicates using Power Query

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.

Power BI Group By earliest date
Power BI Group By the earliest date

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.
Power BI Group By first date
Power BI Group By the first date

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.

Power BI Group By first value
Power BI Group By first value
  • 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.
Power BI Group By first value using DAX
Power BI Group By first value using DAX

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.
Power BI Group By financial year
Power BI Group By financial year

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

Power BI Group By financial year using Power Query
Power BI Group By financial year using 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.
Power BI graph Group By month and year
Power BI graph Group By month and year

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

Power BI graph Group By month and year using Power Query
Power BI graph Group By month and year using Power Query

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.
Power BI graph Group By week
Power BI graph Group By week
  • Now the Groups window will open, -> Name it as ‘Week’ -> Group type is Bin -> Set the Bin size as 7 days. Click on Ok.
Micosoft Power BI graph Group By week
Microsoft Power BI graph Group By week
  • 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.
Micosoft Power BI graph Group date By week
Microsoft Power BI graph Group date By week

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

See also  Power Query Create Table in Power BI + Examples

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.
Power BI Group By hour
Power BI Group By hour
  • Next, in the table, you can see the total sales are grouped by the hour using Power Query.
Power BI Group By hour using Power Query
Power BI Group By 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.

Power BI Group By latest date
Power BI Group By latest date
  • 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.
Microsoft Power BI Group By latest date
Microsoft Power BI Group By latest date

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

Microsoft Power BI Group By latest date using Power Query
Microsoft Power BI Group By latest date 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.

Power BI Group By rank
Power BI Group By rank
  • 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.
Power BI Group By rank using DAX
Power BI Group By rank using DAX

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.

Power BI Group By last value
Power BI Group By last value
  • 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.
Power BI Group By last value using DAX
Power BI Group By last value using DAX

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.

Power BI Group By max value
Power BI Group By max value
  • 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.
Microsoft Power BI Group By max value
Microsoft Power BI Group By max value

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

Microsoft Power BI Group By max value using power Query
Microsoft Power BI Group By max value using power Query

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.

Power BI Group By number range
Power BI Group By number range

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.
Power BI Group By number range using DAX
Power BI Group By number range using DAX

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.

BI Group By the top
BI Group By the top
  • 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.
BI Group By the top using DAX
BI Group By the top using DAX

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.

Power BI Group By percentage
Power BI Group By percentage

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.
Power BI Group By percentile
Power BI Group By percentile
  • 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.
Power BI Group By percentage using DAX
Power BI Group By percentage using DAX

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.

Power BI Group By summarize
Power BI Group By summarize
  • 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.
Power BI Group By summarize using DAX
Power BI Group By summarizing using DAX

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.

Power BI Group By standard deviation
Power BI Group By standard deviation
  • 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.
Microsoft Power BI Group By standard deviation
Microsoft Power BI Group By standard deviation

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.

Microsoft Power BI Group By standard deviation using Power Query
Microsoft Power BI Group By standard deviation using Power Query

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.

Power BI Group By sort
Power BI Group By sort
  • 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.
Microsoft Power BI Group By sort
Microsoft Power BI Group By sort

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

 Group By sort in Microsoft Power BI
Group By sort in Microsoft Power BI

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.

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

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
Microsoft Power BI group by example.

Also, You may like the below Power BI articles:

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
>