Power BI Sum Group by

This Power BI tutorial explains how we can Sum and display the values using group by function. And also we will learn how we can sum the values using the Power Query group by function. Additionally, we will also cover the below-mentioned topics:

1. Power Bi sum group by
2. Power Bi sum group by column
3. Power Bi sum group by multiple columns
4. Power Bi sum group by month
5. Power Bi sum group by year
6. Power Bi sum group by date
7. Power Bi sum group by DAX
8. Power Bi group by sum and average
9. Power Bi sum group by id
10. Power Bi sum group by filter
11. Power Bi new column sum group by
12. Power Bi sum group by multiple columns with filters
13. Power Bi sum group by 2 columns
14. Power Bi sum group by another table
15. Power query cumulative sum by group and date
16. Power query group by sum if
17. Power Bi sum distinct groups by

Power Bi sum group by

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

Also read: Power BI Group By [With 51 real examples]

Power Bi sum group by column

Let us see how we can sum and group by column in Power BI.

For example, here we will use the below table to sum and group the Sales based on the Product or Country column.

• In the Power BI desktop click on the Transform Data from the ribbon, to open the power query editor.

• In the power query editor, Click on Transform tab -> Group by. click on Group By icon from the ribbon.

• Then the Group By window will appear, Click on the Basic options -> choose the Country column.
• Next, enter the new column name, select Operation as Sum, and select the Column as Sales. Then click on the OK button.

• Now, in the below screenshot, you can see that the values are summed and grouped based on the country.

=Table.Group(#"Changed Type",{"Country"},{{"Count",each List.Sum([Sales]),type nullable number}})

This is how to sum group by column in the Power BI.

Also Read: Power BI Report Export to PDF [With 20+ Examples]

Power Bi sum group by multiple columns

Let us see how we can sum and group by Multiple columns in Power BI.

• Here also I have the same sales data to sum and group the Sales based on the Product and Country.

• In the Power BI desktop click on the Transform Data from the ribbon, which redirects to the power query editor.
• In the power query editor, Click on Transform tab -> Group by. click on Group By icon from the ribbon.

=Table.RenameColumns(#"Changed Type",{{"Sales","Sales Value"}})

• Then the Group By window will appear, Click on the Advanced options, -> choose the Country column -> click on Add grouping -> the select Product column as shown below:
• Next, enter the new column name, select Operation as Sum, and select the Column as Sales. Then click on the OK button.

• Now, in the below screenshot, you can see that the two columns are grouped and display the Sales value.

• This is how to sum and group by Multiple columns in Power BI.

Check out: Power BI Add Calculated Column [With Various Examples]

Power Bi sum group by month

Let us see how we can sum group by function to calculate based on the month in Power BI,

Here in this example, I have used the financial table data, to sum the sales values used by Group by function in Power BI.

• Once the data is loaded to the Power BI desktop, create a New Calculated Table, and in the Formula box, write the below formula:

Group by month = GROUPBY(financials,financials[Month Name], " Sales count by Month", SUMX(CURRENTGROUP(), SUM(financials[ Sales])))

Where,

• Group by month = Measure Name
• financials = Table Name
• Month Name and Sales = Column Name

• Now in the below screenshot, you can see that the newly created calculated table contains total sales based on the month presented in the financials table in Power BI.

This is how to sum group by function to calculate based on the month in the Power BI.

Read: Power BI Matrix Multiple Column

Power Bi sum group by year

Let us see how we can sum and group by based on the year in Power BI.

• Here also I have used the financials data to sum and group the Profit based on the year.

• In the Power BI desktop click on the Transform Data from the ribbon, which redirects to the power query editor.
• In the power query editor, Click on Transform tab -> Group by. click on Group By icon from the ribbon.

• Then the Group By window will appear, Click on the Basic options, -> choose the Year column.
• Next, enter the new column name, select Operation as Sum, and select the Column as Profit. Then click on the OK button.

• Now, in the screenshot below, you can see that the sum group calculated the profit and displays the value in Power BI.

=Table.Group(#"Renamed Columns3",{ "Year"},{{"Total Profit", each List.Sum([Profit]),type nullable number}})

This is how we can sum and group based on the year in Power BI.

Check: How to remove rows in power query editor [With various examples]

Power Bi sum group by date

Let us see how we can sum and group based on the date in Power BI.

• Here also I have the Sales data to sum and group the Sales based on the date, we will sum the sales value grouped by date.

• In the Power BI desktop click on the Transform Data from the ribbon, which redirects to the power query editor.
• In the power query editor, Right Click on the date column and select Group by option as shown below:

• Then the Group By window will appear, Click on the Advanced options, -> choose the Country column -> click on Add grouping -> the select Product column as shown below:
• Next, enter the new column name, select Operation as Sum, and select the Column as Sales. Then click on the OK button.

• Now, in the below screenshot, you can see that the sum group calculated the sales value based on the date and displays the value in Power BI.

=Table.Group(#"Changed Type",{ "Date"},{{"Sales Count", each List.Sum([Sales]),type nullable number}})

This is how to sum and group based on the date in Power BI.

Check out: Power BI Create Table From Another Table

Power Bi sum group by DAX

Let us see how we can sum and group by DAX or measure in Power BI.

Here in this example, I have used the financial table data, to sum the sales values used by Group by function in Power BI.

• Once the data is loaded to the Power BI desktop, create a New Calculated Table, and in the Formula box, write the below formula:

Total Sales = SUMMARIZE(financials,financials[Product],"Total Sales",CALCULATE(SUM(financials[ Sales]),ALLEXCEPT(financials,financials[Product])))

Where,

1. Total Sales = Measure Name
2. financials = Table Name
3. Total Sales = New column name
4. Sales = Column Name

• Now in the below screenshot, you can see that the newly created calculated table contains total sales based on the Product presented in the financials table in Power BI.

This is how to sum and group by DAX or measure in Power BI.

Read: Power BI filter between two dates [With 15+ Examples]

Power Bi group by sum and average

Let us see how we can sum and Average the group based on the Country in Power BI.

• Here also I have used the Sales data to sum and Average group by the Sales based on the Country.
• In the Power BI desktop click on the Transform Data from the ribbon, which redirects to the power query editor.
• In the power query editor, Right Click on the Country column and select Group by option as shown below:

• Then the Group By window will appear, Click on the Advanced options, -> choose the Country column.
• Next, enter the new column name, select Operation as Sum, and select the Column as Sales.
• Again click on the Add aggregation, enter the new column name, select Operation as Average, and select the Column as Sales. Then click on the OK button.

• Now, in the below screenshot, you can see that the columns are grouped and display the Sum and Average value of Sales in Power BI.

=Table.Group(#"Changed Type",{ "Country"},{{"Sum", each List.Sum([Sales]),type nullable number},{"Average",each List.Average([Sales])})

This is how to sum and Average the group based on the Country in Power BI.

Also check: Power BI Sum Multiple columns

Power Bi sum group by id

Let us see how we can sum and group the values based on the ID in Power BI.

• Here also I have used the Product data to sum and group the values based on the ID in Power BI. We will sum the sold Amount based on the Accessory ID.

• In the Power BI desktop click on the Transform Data from the ribbon, which redirects to the power query editor.
• In the power query editor, Right Click on the Accessory column and select Group by option as shown below:

• Then the Group By window will appear, Click on the Basic options, -> choose the Country column.
• Next, enter the new column name, select Operation as Sum, and select the Column as Sales. Then click on the OK button.

• Now, in the below screenshot, you can see that the columns are grouped and display the Sum value of Sold Amount in Power BI.

=Table.Group(#"Changed Type",{ "Accessory"},{{"Count", each List.Sum([Sold Amount]),type nullable number}})

This is how to sum and group the values based on the ID in Power BI.

Read: Power BI divides two columns [With Examples]

Power Bi sum group by filter

Let us see how we can sum group by and apply a filter to check the greater than and lesser than values in Power BI,

Here in this example, I have used the products table data, to sum the sales values used by Group by function in Power BI.

• Once the data is loaded to the Power BI desktop, create a New Calculated Measure, and in the Formula box, write the below formula:

Total Count = CALCULATE(SUM(Products[Count]),ALLEXCEPT(Products,Products[Count]))

Where,

1. Total Count = Measure,
2. Products = Table Name,
3. Count = Column

• Once the data is loaded to the Power BI desktop, create a New Calculated Table, and in the Formula box, write the below formula:

Filter = IF(Products[Total Count]>5000,1,0)

Where,

1. Filter = Table Name,
2. Total Count = Measure Name

• Now in the below screenshot, you can see that the newly created calculated table displays the value is greater than 5000 is 1 and if it is less than 5000 is 0.

This is how to sum group by and apply a filter to check the greater than and lesser than values in Power BI.

Read: Power BI Compares Two Columns in Different Tables

Power Bi new column sum group by

Let us see how we can sum group by and display the value in a new column in Power BI,

Here in this example, I have used the products table data, to sum the total Qty values used by Group by function in Power BI.

• In the Power BI desktop click on the Transform Data from the ribbon, which redirects to the power query editor.
• In the power query editor, Right Click on the Product column and select Group by option as shown below:

• Then the Group By window will appear, Click on the Basic options, -> choose the Product column.
• Next, enter the New column name as per the requirement, select Operation as Sum, and select the Column as Profit. Then click on the OK button.

• Now, in the below screenshot, you can see that the sum group is calculated as the total quantity value and displays the value in Power BI.

=Table.Group(#"Changed Type",{ "Product"},{{Total Qty Value", each List.Sum([Total Qty]),type nullable number}})

This is how to sum group by and display the value in a new column in Power BI.

Check: Stacked Bar Chart in Power BI [With 27 Real Examples]

Power Bi sum group by multiple columns with filters

Let us see how we can sum and group by multiple columns with filters in Power Bi.,

Here I have used the three tables as Product set 1, Product set 2, and Product set 3. And Here you can download the Product set Table.

The product set 1:

The product set 2:

The product set 3:

• In the Power BI desktop load the data and click on the Transform Data from the ribbon, which redirects to the power query editor.
• In the power query editor, expand the Append Queries and Click on the Append Queries as a New option:

• In the Append Query window, select the three or more tables option and choose the tables that you want to append, and click on the ok button as shown below.

• Append Queries Table, you can see that the three tables are appended or combined as mentioned below:

=Table.Combine ({#"Product Set1 ", #"Product set 2",#"Product set 3"})

• Once the tables are appended, select the Product ID column and under the Transform option from the ribbon, click on the Merge column.

• And the columns have been merged based on the Product ID, and click on the Home option from the ribbon, and click on the Group by option.
• Then the Group By window will appear, Click on the Basic options, -> choose the Merged column.
• Next, enter the new column name, select Operation as Sum, and select the Column as Sales. Then click on the OK button.

• In the below screenshot, you can see that the columns have been merged, grouped, and displayed the sum values. And also it has been sorted in ascending order.
• Click on close and Apply, so that the changes will appear on the Power BI desktop.

=Table.Sort(#"Grouped Rows",{{"Merged",Order.Ascending}})

This is how to sum and group by multiple columns with filters in Power BI.

Check: Power BI Bookmarks [With 21 Examples]

Power Bi sum group by 2 columns

Let us see how we can sum the group based on two columns in Power BI,

• For example, here we will use the below Product table to sum and group the Sold Amount based on the Product and Accessory column.
• In the Power BI desktop click on the Transform Data from the ribbon, to open the power query editor.
• In the power query editor, Click on Transform tab -> Group by. click on Group By icon from the ribbon.

• Then the Group By window will appear, Click on the Advanced options, -> choose the Accessory column -> and click on the Add grouping -> then select the Product column as shown below:
• Next, enter the new column name, select Operation as Sum, and select the Column as Sold Amount. Then click on the OK button.

• Now, in the below screenshot, you can see that the values are summed and grouped based on the Product column and Accessory column.

=Table.Group(#"Changed Type",{ "Accessory","Product"},{{"Sales Count", each List.Sum([Sales]),type nullable number}})

This is how to sum the group based on two columns in Power BI.

Read: Clustered Column Chart in Power BI [With 45 Real Examples]

Power Bi sum group by another table

Let us see how we can sum and group by another table in Power Bi,

Here I have used the sales table, make sure one column is common in both the tables. In this example, the country column is common in both the tables:

Table:1

Table:2

• In the Power BI desktop load the data and click on the Transform Data from the ribbon, which redirects to the power query editor.
• In the power query editor, expand the Append Queries and Click on the Append Queries option:

• In the Append Query window, select the two tables option and choose the tables that you want to append, and click on the ok button as shown below.

• Once the tables are appended, select the Product ID column and under the Transform option from the ribbon, click on the Merge column.

• And the columns have been merged based on the Product ID, and click on the Home option from the ribbon, and click on the Group by option.
• Check the data type, it should be a number data type so that we can sum the values, if it text data type then change it to the Number data type.
• Then the Group By window will appear, Click on the Basic options, -> choose the Sales Amount column.
• Next, enter the new column name, select Operation as Sum, and select the Column as Sales. Then click on the OK button.

• In the below screenshot, you can see that the columns have been merged, grouped, and displayed the sum values. And also it has been sorted in ascending order.
• Click on close and Apply, so that the changes will appear on the Power BI desktop.

This is how to sum and group by another table in Power BI.

Check: Power BI Slicer Multiple Columns

Power query cumulative sum by group and date

Let us see how we can cumulative sum by group and date using Power query in Power BI,

Here in this example, I have used the below Product table data, to the cumulative sum of the running total by Group by function in Power BI.

• In the Power BI desktop click on the Transform Data from the ribbon, which redirects to the power query editor.
• In the Power Query editor, under the Add column option, Expand the Index column and select the index option from 1 as shown below:
• The index column will be added to the Table data.

• Now in the Power Bi desktop, Add a new column and apply the below-mentioned DAX formula:

GroupSum = 
SUMX (
    FILTER (
        Products,
        EARLIER ( Products[Product] ) = Products[Product]
            && EARLIER (Products[Index] ) >= Products[Index]
    ),
Products[Sold Amount]
)

Where,

1. GroupSum = Column Name
2. Products = Table Name
3. Product, Index, Sold Amount = Column names

• In the below screenshot, you can see that the group’s column displays the cumulative sum or the running total of the Sold Amount based on the Product Grouped by.

This is how to cumulative sum by group and date using Power query in Power BI.

Check this post: Power BI if date + 27 Examples

Power query group by sum if

Let us see how we can group by sum if using Power query in Power BI,

Here in this example, I have used the below Sales Product table data, to group by sum if using Power Query in Power BI.

• In the Power BI desktop click on the Transform Data from the ribbon, which redirects to the power query editor.
• From the order Date, extract the year column and select the group by option from the Power Query editor ribbon as shown below:

• Then the Group By window will appear, Click on the Advanced options, -> choose the Year column and Product Column.
• Next, enter the new column name, select Operation as Sum, and select the Column as Sales and Quantity.
• Again click on the Add aggregation, enter the new column name, select Operation as Sum, and select the Column as Sales. Then click on the OK button.

• Then the Group By window will appear, Click on the Basic options -> choose the Year column.
• Next, enter the new column name, select Operation as Sum, and select the Column as Sales. Then click on the OK button.
• Now, in the below screenshot, you can see that the columns are grouped and display the Sum value of Sales based on the year in Power BI.

=Table.Group(#"Changed Type",{ "Year"},{{"Sales Count", each List.Sum([Sales]),type nullable number}})

This is how to group by sum if using Power query in Power BI.

Also read: Power BI IF + 31 Examples

Power Bi sum distinct groups by

Let us see how we can count or sum distinct values using group by feature in Power BI,

In this example, I am going to use the products table data to sum or count the distinct values using group by feature in Power BI.

• In the Power BI desktop click on the Transform Data from the ribbon, which redirects to the power query editor.
• Select the product column and click on the group by option from the Power Query editor ribbon as shown below:

• Then the Group By window will appear, Click on the Basic options -> choose the Product column.
• Next, enter the new column name, select Operation as Count Distinct Rows, and then click on the OK button.

• In the below screenshot, you can see that the new column displays the count of the distinct values presented in the table.

=Table.Group(#"Changed Type",{ "Product"},{{"Count", each Table.RowCount(Table.Distinct(_)),Int64.Type})

This is how to count or sum distinct values using group by feature in Power BI.

Also, you may like the below Power BI Tutorials:

• How to merge columns in Power Query
• How to Merge Column in Power BI
• Power bi change color based on value [With 13 real examples]
• Power BI calculated column [With 71 Useful Examples]
• Power BI sync slicers [With 15 useful examples]
• Power bi conditional column [With 17 Useful Examples]
• Power BI Date Slicer [With 41 real examples]

In this Power BI Tutorial, we have learned how to Sum and display the values using group by feature. And also we covered the below-mentioned headings:

• Power Bi sum group by column
• Power Bi sum group by multiple columns
• Power Bi sum group by month
• Power Bi sum group by year
• Power Bi sum group by date
• Power Bi sum group by DAX
• Power Bi group by sum and average
• Power Bi sum group by id
• Power Bi sum group by filter
• Power Bi new column sum group by
• Power Bi sum group by multiple columns with filters
• Power Bi sum group by 2 columns
• Power Bi sum group by another table
• Power query cumulative sum by group and date
• Power query group by sum if
• Power Bi sum distinct groups by