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.

Power BI Sum Group by Multiple columns
Power BI Sum Group by column
  • In the Power BI desktop click on the Transform Data from the ribbon, to open the power query editor.
Sum Group by Multiple columns in Power Bi
Sum Group by Multiple columns in Power Bi
  • In the power query editor, Click on Transform tab -> Group by. click on Group By icon from the ribbon.
Sum Group by Multiple columns in the Power Bi
Sum Group by columns in the Power Bi
  • 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.
Sum Group by Multiple columns in Power Bi example
Sum Group by columns in Power Bi example
  • 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}})
Sum Group by Multiple columns in the Power Bi example
Sum Group by columns in the Power Bi example

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"}})
sum group by values in multiple columns
sum group by values in multiple columns
  • 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.
Power BI Sum Group by Multiple columns example
Power BI Sum Group by Multiple columns example
  • Now, in the below screenshot, you can see that the two columns are grouped and display the Sales value.
Power BI Sum Group by columns
Power BI Sum Group by columns
  • 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.
Power Bi sum group by month
Power Bi sum group by month

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.
Power Bi sum group by year
Power Bi sum group by year
  • 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.
Power Bi sum group by year example
Power Bi sum group by year example
  • 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}})
sum group by year in Power Bi
sum group by year in Power Bi

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.
Sum group by date in Power Bi
Sum group by date 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 date column and select Group by option as shown below:
Sum group by date in the Power Bi
Sum group by date in the Power Bi
  • 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.
Sum group by date in the Power Bi example
Sum group by date in the Power Bi example
  • 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}})
Sum group by date in Power Bi example
Sum group by date in Power Bi example

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.
Power Bi sum group by DAX
Power Bi sum group by DAX

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:
Power Bi group by sum and average
Power Bi group by sum and average
  • 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.
Power Bi group by sum and average example
Power Bi group by sum and average example
  • 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])})
Example of Power Bi group by sum and average
Example of Power Bi group by sum and average

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.
Power Bi sum group by id
Power Bi sum group by 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:
Power Bi sum group by id example
Power Bi sum group by id example
  • 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.
Example of Power Bi sum group by id
Example of Power Bi sum group by id
  • 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}})
Example of the Power Bi sum group by id
Example of the Power Bi sum group by id

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.
Power Bi sum group by filter
Power Bi sum group by filter

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:
Power Bi new column sum group by
Power Bi new column sum group by
  • 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.
Power Bi new column sum group by example
Power Bi new column sum group by example
  • 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}})
Example of Power Bi new column sum group by
Example of Power Bi new column sum group by

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:

Power Bi sum group by multiple columns in table
Power Bi sum group by multiple columns in the table

The product set 2:

Power Bi sum group by multiple columns in the table
Power Bi sum group by multiple columns in the table

The product set 3:

sum group by multiple columns in the Power Bi
sum group by multiple columns in the Power Bi
  • 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:
Power Bi sum group by multiple columns with filters
Power Bi sum group by multiple columns with filters
  • 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.
sum group by multiple columns with filters
sum group by multiple columns with filters
  • 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"})
sum group by multiple columns with filters example
sum group by multiple columns with filters example
  • Once the tables are appended, select the Product ID column and under the Transform option from the ribbon, click on the Merge column.
sum group by multiple columns with filters in Power BI example
sum group by multiple columns with filters in Power BI example
  • 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.
sum group by multiple columns with filter Power BI example
sum group by multiple columns with filter Power BI example
  • 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}})
sum group by multiple columns and filters
sum group by multiple columns and filters

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.
Power Bi sum group by 2 columns
Power Bi sum group by 2 columns
  • 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.
Power Bi sum group by 2 columns example
Power Bi sum group by 2 columns example
  • 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}})
sum group by 2 columns in Power Bi
sum group by 2 columns in Power Bi

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

Power Bi sum group by another table
Power Bi sum group by another table

Table:2

Power Bi sum group by another table example
Power Bi sum group by another table example
  • 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:
Power Bi sum group by another table append
Power Bi sum group by another table append
  • 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.
Power Bi sum group by another table append query
Power Bi sum group by another table append query
  • Once the tables are appended, select the Product ID column and under the Transform option from the ribbon, click on the Merge column.
Power Bi sum group by another table Merged
Power Bi sum group by another table Merged
  • 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.
sum group by another table in Power Bi
sum group by another table in Power Bi
  • 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.
sum group by another table in Power Bi example
sum group by another table in Power Bi example

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.

Power query cumulative sum by group and date
Power query cumulative sum by group and 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, 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.
Power query cumulative sum by group
Power query cumulative sum by group
  • 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.
Power query cumulative sum by group example
Power query cumulative sum by group example

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.

Power query group by sum if
Power query group by sum if
  • 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:
Power query group by sum if example
Power query group by sum if example
  • 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.
Power query group by sum if example in Power BI
Power query group by sum if example in Power BI
  • 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}})
Power query group by sum if example in the Power BI
Power query group by sum if example in the Power BI

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.

Power Bi sum distinct groups by
Power Bi sum distinct groups by
  • 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:
Power Bi sum distinct groups by example
Power Bi sum distinct groups by example
  • 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.
Power Bi sum distinct group by example
Power Bi sum distinct groups by example
  • 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})
sum distinct groups by in Power Bi
sum distinct groups by in Power Bi

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:

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
  • Quite honestly, PowerBI is by far the greatest piece of on this planet. Everything is so super complicated and those bloody mf’s over at MS have not spent one second thinking about user experience. Things I can do in tools like KNIME in secs take extensive formulas in PowerBI. Hate this tool from the depth of my heart. How can you promote such a bunch of bs?

  • >