Power BI DAX: SUMMARIZE, SUM
Mohamad's interest is in Programming (Mobile, Web, Database and Machine Learning). He is studying at the Center For Artificial Intelligence Technology (CAIT), Universiti Kebangsaan Malaysia (UKM).
SalesSummary =
SUMMARIZE ( sales, Date[Year], "Sales", SUM ( Sales[Sale] ) )
The SUMMARIZE function in DAX is a tool for creating summary tables. It allows you to group data by one or more columns and then apply aggregate functions like SUM, AVERAGE, COUNT, etc., to create new columns with summarized data.
Example Scenario
Date Table
| Date | Month | Year |
| 2024-01-01 | January | 2024 |
| 2024-02-01 | February | 2024 |
| 2024-03-01 | March | 2024 |
| 2023-01-01 | January | 2023 |
| 2023-02-01 | February | 2023 |
| 2023-03-01 | March | 2023 |
Sales Table
| Date | Sale |
| 2024-01-01 | 1000.00 |
| 2024-02-01 | 1500.00 |
| 2024-03-01 | 2000.00 |
| 2023-01-01 | 1200.00 |
| 2023-02-01 | 1300.00 |
| 2023-03-01 | 1400.00 |
Creating the Calculated Table To create a calculated table that includes a row for each year and a column with the total sales per year, you can use the following DAX expression:
YearlySales =
SUMMARIZE ( Sales, 'Date'[Year], "Total Sales", SUM ( Sales[Sale] ) )
How It Works
1. Grouping by Year: The SUMMARIZE function groups the data by the Year column from the Date table.
2. Calculating Total Sales: For each group (year), it calculates the total sales using the SUM function on the Sale column from the Sales table.
3. Creating the Summary Table: The result is a new table with a row for each year and a column named "Total Sales" that contains the sum of sales for that year.
Resulting Table
| Year | Total Sales |
| 2024 | 4500.00 |
| 2023 | 3900.00 |
Integrating Other Functions
You can integrate other aggregate functions in a similar way. For example, to calculate the average sales per year, you can use the AVERAGE function:
YearlySales = SUMMARIZE( Sales, 'Date'[Year], "Total Sales", SUM(Sales[Sale]), "Average Sales", AVERAGE(Sales[Sale]) )
This will add another column "Average Sales" to the YearlySales table, showing the average sales for each year.