Power BI Sum Group by

This Power BI tutorial will teach us to sum and display values using the group by function and how to sum values with the Power Query group by function.

Additionally, we will discuss the topics below:

• Power BI sum group by column
• Power BI sum group by DAX
• Power BI sum group by multiple columns
• Power BI: measure sum by group
• Power BI DAX sum group by with filter

Power BI Sum Group By Column

Imagine you’re an analyst for an online retail store. You want to understand your sales performance across different product categories. You have a SharePoint list with product details, such as category (e.g., Clothing, Electronics, Books, Beauty), Sales Quantity, and Sales Amount for each product.

You want to calculate the total sales for each product category.

Now follow the below steps to do this:

1. First, open Power BI Desktop and load the SharePoint list. Then, you will see the dataset in the Data pane.

2. Under the Home tab, click Transform date.

3. Under the Transform tab, click the Group By option in the Power Query Editor.

4. Once the Group By window opens, choose the “Basic” option. After that, select the “Product Category” columns. Next, name the column and choose “Sum” as the operation. Finally, select the “Sales Amount” column. Then click OK.

5. You can see the Total Sales value based on the Product category column using Power Query.

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

Power BI Sum Group By using DAX

Suppose you are working as a Data Analyst at an IT company that sells various software and hardware products and provides some services. The company wants to analyze the sales performance of its products, specifically to understand the total sales amount for each product category.

According to this scenario, we have a dataset in Power BI. Please check the screenshot below.

Now follow the below steps:

Navigate to the Table view and click “New table” under the Home tab.

Then, in the formula bar, put the below DAX expression.

SalesSummary = 
GROUPBY(
    'Product Sales',
    'Product Sales'[Category],
    "TotalSalesAmount", SUMX(CURRENTGROUP(), 'Product Sales'[Quantity Sold] * 'Product Sales'[Unit Price])
)

Where:

• SalesSummary = This part names the table we create, summarizing sales data by category.
• GROUPBY() = This function creates a summary table that groups data by one or more columns. It allows for custom aggregations within each group.
• ‘Product Sales'[Category] = This specifies the column by which we are grouping the data. In this case, it’s the ‘Category’ column from the ‘Product Sales’ table.
• “TotalSalesAmount” = This is the name of the new column we’re creating within each group. It will contain the total sales amount for that category.
• SUMX(CURRENTGROUP(), ‘Product Sales'[Quantity Sold] * ‘Product Sales'[Unit Price])= This part defines the calculation for the