Introduction to DAX and Power BI

Overview

This guide provides a comprehensive introduction to using the DAX (Data Analysis Expressions) function SUMMARIZE in Power BI. We will cover key concepts, practical implementation, and detailed explanations to help you effectively use the SUMMARIZE function in your Power BI projects.

What is DAX?

DAX stands for Data Analysis Expressions. It is a collection of functions, operators, and constants that can be used in Power BI to perform advanced data manipulation, aggregation, and calculations. DAX is essential for creating custom calculations and insights in your reports.

Using the SUMMARIZE Function

The SUMMARIZE function in DAX is used to group data by one or more columns and can also perform aggregations. It is similar to the SQL GROUP BY statement.

Syntax

SUMMARIZE(
    <table>,
    <groupBy_columnName> [, <groupBy_columnName>] …
    [,<name>, <expression>] …
)

Parameters

• <table>: The name of the existing table to summarize.
• <groupBy_columnName>: One or more columns by which to group the data.
• <name>: The name of the new column or aggregation.
• <expression>: The DAX expression used to calculate the new column or aggregation.

Practical Example

Let’s consider a dataset Sales with the following columns:

• ProductID
• Region
• SalesAmount

We want to summarize the total sales by Region.

Steps

1. Open Power BI Desktop.
2. Load the Sales dataset into Power BI.
3. Create a new table in Power BI using a DAX expression.

DAX Expression

SummarizedSalesByRegion = 
    SUMMARIZE(
        Sales,
        Sales[Region],
        "TotalSales", SUM(Sales[SalesAmount])
    )

This DAX expression creates a new table named SummarizedSalesByRegion that groups the data by Region and calculates the total sales for each region.

Explanation

1. SUMMARIZE Function: This groups the Sales table by the Sales[Region] column.
2. Aggregation: It calculates the sum of SalesAmount for each region and names the new column as TotalSales.

Visualizing the Summarized Data

1. Select the SummarizedSalesByRegion table from the Fields pane.
2. Create a new visual (e.g., a bar chart) and drag the Region field to the Axis area and the TotalSales field to the Values area.
3. Customize the visual as needed to effectively present the summarized data.

Conclusion

This guide has introduced you to the DAX function SUMMARIZE and demonstrated how to apply it in Power BI to group and aggregate data. Following these steps will enable you to create summarized tables and insightful visualizations in your Power BI reports.

Understanding the SUMMARIZE Function

The SUMMARIZE function in DAX is a powerful tool used in Power BI to create a customized summary table from your data. It enables you to aggregate data and group it based on specific columns. Below is a detailed explanation of how to use the SUMMARIZE function, including practical examples.

Syntax

SUMMARIZE (
    <table>,
    <groupBy_columnName>,
    [<groupBy_columnName> [, <groupBy_columnName> [, … ]]],
    [<name>, <expression>] [, <name>, <expression>] [, …]
)

• : The table containing the columns to be summarized.
• : The columns based on which you want to group the data.
• : The name of the new column to be created.
• : The DAX expression that the new column calculates.

Basic Example

Let’s consider a basic example with a Sales table that has the following columns: ProductID, Region, SalesAmount.

Requirement:

Summarize the total SalesAmount for each ProductID in each Region.

Implementation:

EVALUATE
SUMMARIZE (
    Sales,
    Sales[ProductID],
    Sales[Region],
    "TotalSales", SUM(Sales[SalesAmount])
)

In this example, the SUMMARIZE function groups data by ProductID and Region, then calculates the TotalSales as the sum of SalesAmount for each group.

Advanced Example

Assume you have a Sales table with these columns: Date, ProductID, Region, SalesAmount, Quantity.

Requirement:

Summarize the total SalesAmount and average Quantity for each ProductID per Month.

Implementation:

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        Sales,
        Sales[ProductID],
        CALCULATETABLE (
            VALUES( 'Date'[Month] ),
            'Date'
        )
    ),
    "TotalSales", SUM(Sales[SalesAmount]),
    "AverageQuantity", AVERAGE(Sales[Quantity])
)

In this example:

• The SUMMARIZE function groups the data by ProductID and Month.
• The ADDCOLUMNS function enhances this summarized data with new columns:
  • TotalSales: The total SalesAmount for each group.
  • AverageQuantity: The average Quantity for each group.

Filtering with SUMMARIZE

You can also apply filters within the SUMMARIZE function using CALCULATETABLE.

Requirement:

Summarize the total SalesAmount for each ProductID in each Region, but only for sales where the SalesAmount is greater than 100.

Implementation:

EVALUATE
SUMMARIZE (
    FILTER (
        Sales,
        Sales[SalesAmount] > 100
    ),
    Sales[ProductID],
    Sales[Region],
    "TotalSales", SUM(Sales[SalesAmount])
)

In this case, the FILTER function is used to include only the rows where SalesAmount is greater than 100, and then SUMMARIZE is applied to this filtered data.

Combining SUMMARIZE with Other DAX Functions

You can combine SUMMARIZE with other DAX functions to perform complex data manipulations.

Requirement:

Summarize the maximum sales (SalesAmount) for each ProductID in each Region.

Implementation:

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        Sales,
        Sales[ProductID],
        Sales[Region]
    ),
    "MaxSales", CALCULATE ( MAX(Sales[SalesAmount]) )
)

Here, the MAX function is combined with SUMMARIZE to fetch the maximum sales amount for each group defined by ProductID and Region.

Conclusion

The SUMMARIZE function is essential for creating summary tables and performing grouped aggregation in Power BI with DAX. Understanding its syntax and various use cases allows you to manipulate data effectively and extract meaningful insights.

Practical Examples and Applications of SUMMARIZE

Example 1: Summarizing Sales Data by Region and Product

When working with a sales dataset, it might be useful to summarize the total sales, broken down by region and product. The SUMMARIZE function can effortlessly achieve this.

DAX Query

SalesSummary = 
SUMMARIZE(
    Sales, 
    Sales[Region], 
    Sales[Product], 
    "Total Sales", SUM(Sales[Amount])
)

Breakdown

• Sales: The table containing raw sales data.
• Sales[Region]: The column containing the region data.
• Sales[Product]: The column containing product data.
• "Total Sales": The name of the new column for the total sales.
• SUM(Sales[Amount]): The aggregation function to sum sales amounts.

Example 2: Counting Orders by Customer Segment

For a business analysis, summarizing the number of orders by customer segment can provide valuable insights.

DAX Query

OrderCountBySegment = 
SUMMARIZE(
    Orders, 
    Orders[CustomerSegment], 
    "Order Count", COUNT(Orders[OrderID])
)

Breakdown

• Orders: The table containing all order details.
• Orders[CustomerSegment]: The column containing customer segment data.
• "Order Count": The name of the new column for the count of orders.
• COUNT(Orders[OrderID]): The aggregation function to count the number of orders.

Example 3: Average Sales Amount per Category and Year

If the analysis requires the average sales amount per product category and year, the SUMMARIZE function can be employed in combination with an average aggregation.

DAX Query

AvgSalesPerCategoryYear = 
SUMMARIZE(
    Sales, 
    Sales[ProductCategory], 
    Sales[Year], 
    "Average Sales", AVERAGE(Sales[Amount])
)

Breakdown

• Sales: The table containing sale transactions.
• Sales[ProductCategory]: The column containing product category data.
• Sales[Year]: The column containing the year of the transaction.
• "Average Sales": The name of the new column for the average sales.
• AVERAGE(Sales[Amount]): The aggregation function to calculate the average sales amount.

Example 4: Obtaining Unique Product Counts by Store

For inventory management purposes, it could be practical to know the number of distinct products available in each store.

DAX Query

UniqueProductsPerStore = 
SUMMARIZE(
    Inventory, 
    Inventory[Store], 
    "Unique Product Count", DISTINCTCOUNT(Inventory[ProductID])
)

Breakdown

• Inventory: The table containing inventory details.
• Inventory[Store]: The column containing store data.
• "Unique Product Count": The name of the new column for the count of unique products.
• DISTINCTCOUNT(Inventory[ProductID]): The aggregation function to count distinct product IDs.

Example 5: Revenue Contribution by Department

Analyzing revenue contributions by each department can be done by summarizing the revenue data accordingly.

DAX Query

RevenueByDepartment = 
SUMMARIZE(
    Revenue, 
    Revenue[Department], 
    "Revenue Contribution", SUMX(Revenue, Revenue[UnitPrice] * Revenue[Quantity])
)

Breakdown

• Revenue: The table with revenue details.
• Revenue[Department]: The column containing department data.
• "Revenue Contribution": The name of the new column for revenue contribution.
• SUMX(Revenue, Revenue[UnitPrice] * Revenue[Quantity]): The expression to calculate total revenue by multiplying unit price by quantity.

Example 6: Employee Sales Performance by Quarter

To assess sales performance on a quarterly basis for each employee, use the SUMMARIZE function.

DAX Query

EmployeeSalesByQuarter = 
SUMMARIZE(
    Sales, 
    Sales[EmployeeName], 
    Sales[Quarter], 
    "Total Sales", SUM(Sales[Amount])
)

Breakdown

• Sales: The table with sales data.
• Sales[EmployeeName]: The column with employee names.
• Sales[Quarter]: The column with quarterly data.
• "Total Sales": The name of the new column for total sales per quarter.
• SUM(Sales[Amount]): The aggregation function to sum the sales amount by each employee per quarter.

These examples provide real-world applications and can be modified to fit various datasets and analytical needs within Power BI.

Advanced Techniques with SUMMARIZE

The SUMMARIZE function in DAX can become more powerful through the use of advanced techniques. In this section, we’ll explore practical implementations.

Nested SUMMARIZE

It’s possible to use SUMMARIZE within another SUMMARIZE to aggregate data at multiple levels.

EVALUATE
SUMMARIZE(
    SUMMARIZE(
        Sales,
        Sales[ProductKey],
        Sales[DateKey],
        "TotalSales", SUM(Sales[Quantity])
    ),
    [ProductKey],
    "TotalProductSales", SUMX(CURRENTGROUP(), [TotalSales])
)

Explanation:

• The first SUMMARIZE calculates TotalSales for each combination of ProductKey and DateKey.
• The outer SUMMARIZE then summarizes the results by ProductKey to find TotalProductSales.

Calculated Columns within SUMMARIZE

You can add calculated columns directly in the SUMMARIZE function.

EVALUATE
SUMMARIZE(
    Sales,
    Sales[ProductKey],
    "TotalSales", SUM(Sales[Quantity]),
    "AveragePrice", AVERAGE(Sales[UnitPrice] * Sales[Quantity])
)

Explanation:

• This calculates the TotalSales and the AveragePrice for each ProductKey directly within the SUMMARIZE function.

Combining SUMMARIZE with RELATEDTABLE

Use RELATEDTABLE to pull in related data for a more comprehensive summary.

EVALUATE
SUMMARIZE(
    Sales,
    Sales[ProductKey],
    "ProductCategory", DISTINCT(RELATEDTABLE(ProductCategory[CategoryName])),
    "TotalSales", SUM(Sales[Quantity]),
    "AveragePrice", AVERAGE(Sales[UnitPrice])
)

Explanation:

• The RELATEDTABLE function pulls in related category information for each product.
• This allows for adding more context to the summarized data.

Using SUMMARIZE with ROLLUPGROUP

The ROLLUPGROUP function allows for subtotals or grand totals.

EVALUATE
SUMMARIZE(
    Sales,
    ROLLUPGROUP(Sales[ProductKey], Sales[DateKey]),
    "TotalSales", SUM(Sales[Quantity])
)

Explanation:

• ROLLUPGROUP adds subtotals for each grouping level.
• This results in a summarized table that includes subtotals for each ProductKey along with its DateKey level.

Switching Context with SUMMARIZE

Use the SWITCH function to dynamically change the grouping context.

VAR GroupByCategory = 
    SUMMARIZE(
        Sales, 
        Sales[ProductCategoryKey], 
        "TotalSales", SUM(Sales[Quantity])
    )

VAR GroupByProduct =
    SUMMARIZE(
        Sales, 
        Sales[ProductKey], 
        "TotalSales", SUM(Sales[Quantity])
    )

RETURN
SWITCH(
    TRUE(),
    [SelectedGrouping] = "Category", GroupByCategory,
    [SelectedGrouping] = "Product", GroupByProduct
)

Explanation:

• Multiple summarizations are stored in variables.
• The SWITCH function then dynamically selects the summarization result based on the SelectedGrouping.

By employing these advanced techniques with SUMMARIZE, you can create sophisticated and flexible data aggregations in Power BI.

Combining SUMMARIZE with Other DAX Functions

In this section, we demonstrate how to combine the SUMMARIZE function with other DAX functions to create powerful and insightful data visualizations. The practical implementation below will illustrate how you can use the SUMMARIZE function alongside CALCULATE and ADDCOLUMNS.

Example: Combining SUMMARIZE with CALCULATE and ADDCOLUMNS

Let’s assume we have a dataset representing sales transactions with the following columns: SalesDate, ProductID, SalesAmount, and SalesQuantity. We want to summarize the total sales amount and total quantity sold, grouped by the ProductID, and also calculate the average sales amount per quantity for each product.

Step-by-Step Implementation

1. Use SUMMARIZE to Group by ProductID

   
   

   ProductSalesSummary = 
   SUMMARIZE(
       Sales,
       Sales[ProductID],
       "TotalSalesAmount", SUM(Sales[SalesAmount]),
       "TotalSalesQuantity", SUM(Sales[SalesQuantity])
   )
   

   
   

2. Calculate Average Sales Amount per Quantity
   Use ADDCOLUMNS to add a new column that calculates the average sales amount per quantity:

   
   

   ProductSalesSummaryWithAvg = 
   ADDCOLUMNS(
       ProductSalesSummary,
       "AvgSalesAmountPerQuantity", 
       [TotalSalesAmount] / [TotalSalesQuantity]
   )
   

   
   

3. Additional Filter and Aggregation Using CALCULATE
   Suppose we want to include only those products where the total sales amount is greater than a specific threshold (e.g., 1000):

   
   

   FilteredProductSalesSummary = 
   FILTER(
       ProductSalesSummaryWithAvg,
       [TotalSalesAmount] > 1000
   )
   

   
   

4. Putting it All Together
   Combine the previous steps to show the complete implementation.

   
   

   ProductSalesSummaryEnhanced = 
   FILTER(
       ADDCOLUMNS(
           SUMMARIZE(
               Sales,
               Sales[ProductID],
               "TotalSalesAmount", SUM(Sales[SalesAmount]),
               "TotalSalesQuantity", SUM(Sales[SalesQuantity])
           ),
           "AvgSalesAmountPerQuantity", 
           [TotalSalesAmount] / [TotalSalesQuantity]
       ),
       [TotalSalesAmount] > 1000
   )
   

   
   

Explanation of the Implementation

• SUMMARIZE: The initial step groups the sales data by ProductID and calculates the total sales amount and quantity for each product.
• ADDCOLUMNS: Adds a computed column (AvgSalesAmountPerQuantity) that calculates the average sales amount per quantity for each product.
• FILTER: Applies a filter to restrict the result to only those products where the total sales amount is greater than 1000.
• Combining Functions: By nesting these functions, you create a powerful summarized table that can be used directly in Power BI for further analysis or visualization.

Practical Usage

This implementation can be directly used in Power BI by creating a new table with the DAX expression provided. It provides a summarized view, which is extremely useful for generating insights, especially in creating key performance indicators (KPIs) and dashboards in Power BI.

By combining SUMMARIZE with CALCULATE and other DAX functions, you can leverage the full potential of DAX to perform complex data transformations and analyses in a simple and efficient manner.