How to Use GROUP BY in DAX & Calculate Sum of Two Values in Power BI

Disclaimer/Disclosure: Some of the content was synthetically produced using various Generative AI (artificial intelligence) tools; so, there may be inaccuracies or misleading information present in the video. Please consider this before relying on the content to make any decisions or take any actions etc. If you still have any concerns, please feel free to write them in a comment. Thank you.
---

Summary: Learn how to effectively group data and calculate the sum of multiple values in Power BI using DAX, tailored for intermediate and advanced users.
---

How to Use GROUP BY in DAX & Calculate Sum of Two Values in Power BI

Data analysis often requires summarizing or aggregating data to extract meaningful insights. In Power BI, DAX (Data Analysis Expressions) is the powerful language that can handle these tasks. One of the common requirements is to group data by a specific column and then calculate the sum of one or more values. This guide will explore how to do this efficiently.

Understanding Group By in DAX

DAX does not have a direct GROUP BY function as SQL does. Instead, it uses functions such as SUMMARIZE and GROUPBY to achieve the same objective.

Using SUMMARIZE

SUMMARIZE allows you to create a summary table based on specified grouping columns and aggregation functions. Here's a basic example:

[[See Video to Reveal this Text or Code Snippet]]

This DAX query creates a new table grouping the Sales data by the Category column and calculates the total sales for each category.

Using GROUPBY

The GROUPBY function provides a more compact syntax but is often used in conjunction with other functions like SUMMARIZE to produce useful aggregates.

[[See Video to Reveal this Text or Code Snippet]]

Here, GROUPBY groups the Sales table by Category and calculates the sum of SalesAmount for each group using SUMX.

Calculating the Sum of Two Values

To calculate the sum of two different columns after grouping, you need to extend your DAX expressions slightly.

Example with SUMMARIZE

[[See Video to Reveal this Text or Code Snippet]]

This query will return a table summarizing Sales by Category, with both the total sales amount and total units sold calculated.

Example with GROUPBY

[[See Video to Reveal this Text or Code Snippet]]

Again, GROUPBY is used to group by Category and compute sums for SalesAmount and UnitsSold within each group.

Putting It All Together in Power BI

Once your DAX queries are crafted, you can integrate them into your Power BI reports:

Create a New Table: Use the SUMMARIZE or GROUPBY expressions to create new, summarized tables.

Visualize the Data: Add the newly created tables to your reports, using visualizations like bar charts or tables to display the grouped and aggregated data.

Conclusion

Mastering GROUP BY in DAX and calculating the sum of multiple values in Power BI is essential for intermediate and advanced users aiming to perform effective data analysis. The SUMMARIZE and GROUPBY functions offer robust ways to achieve groupings and sums, making your data insights more meaningful and actionable.