How to Perform Aggregation and Summarization in DAX — DAX in Power BI — Chapter 4

Explain with hands-on how grouping and summarizing information is a powerful feature of Power BI.


For any data analysis or business intelligence project, it is the primary requirement to provide summary information from your data in a faster way.

If you have missed my previous blogs about DAX, please check my medium profile. 

Purpose of Summarization

 In any industry, millions of transaction data with thousands of rows per day have been captured. From this type of large table, you can prepare a summary table using aggregation logic and represent summarize visual. 

Performance of visual will be faster if data is fetching from the summary table instead of the detail table which has raw data. You will get more optimize reports for a better experience. 

However, you will lose the ability to cross filter on any level of aggregation which are not included in your summary table.

Get Data

For this blog, I have considered code from one of my case study. 

  1. It is based on US Superstore data. 
  2. Star schema data model is already defined in this code. 
  3. If you want to check how I have converted transactional data to the analytical data model, please check the below link for your reference. https://arpitatechcorner.com/2021/02/26/data-analysis-powerbi-dimensional-queries-decomposition-tree/

Main DAX functions for summarization

The three main functions for summarization are 

  • SUMMARIZE
  • SUMMARIZECOLUMNS
  • GROUP BY

SUMMARIZE

The syntax for SUMMARIZE function is as follows

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

table -> name of the table exists in the data model or calculated table or functions which return table or variables which store table expression

groupBy_columnName → name of any column from a specified table or any column from a table that has a relationship with the first parameter table

name → name for summarizes column with double-quotes.

expression -> DAX expression with aggregation component of the summary table

This function returns a table that can be used for your reporting purpose.

Let us understand the Summarize function with some example.

DAX SUMMARIZE function Power BI
Image by Author

Let’s map the parameters in the above example back to the syntax specification so you can understand how the function works.

DAX Power BI Summarize function
Image by Author

In this DAX function, you have mentioned the fact table, but for the group by column used order date and product dimensions. That means, if in your data model relationship is present, then this function can execute based on that relationship also. 

Now, you can create one Matrix visual to see how your newly created table is behaving.

Image by Author

SUMMARIZE WITH A FILTER

Now If you are adding a filter logic (for example region) in the above DAX calculation, then underlying data for the summary table restricts to that particular filtered value.

DAX Summarize Power BI
Image by Author

Now you can map with DAX syntax

Table → FILTER(‘Fact — Sales’, RELATED(‘Dim — Geography’[Region])=”East”)

groupByColumn → ‘Dim — Order Date’[Year],’Dim — Product’[Category]

Name1, Expression1 → “Summary Sales”, SUM(‘Fact — Sales’[Sales])

Name 2, Expression 2 → “Average Sales”, DIVIDE(SUM(‘Fact — Sales’[Sales]), COUNTROWS(‘Fact — Sales’))

Let’s compare the result based on two reports, one from the summary table and another from the detail fact table with related dimension tables.

DAX Power BI
Image by Author

SUMMARIZECOLUMNS

Another function to build an aggregate table in DAX is the SUMMARIZECOLUMNS function. 

SUMMARIZECOLUMNS( <groupBy_columnName> [, < groupBy_columnName >]…, [<filterTable>]…[, <name>, <expression>]…)

groupBy_columnName → group by columns

filterTable → if you pass FILTER function, it considers either a filter to aggregation or to start adding columns whose result will be DAX expression such as SUM, COUNT and so on. 

name → name for summarizes column with double-quotes.

expression -> DAX expression with aggregation component of the summary table

This function does not need to pass the base table like SUMMARIZE function, however, the results are the same.

To understand in detail let us create one table with SUMMARIZECOLUMNS function but without FILTER function.

DAX Power BI SUMMARIZECOLUMNS
Image by Author

SUMMARIZECOLUMNS WITH A FILTER

Now you can add filter condition in your formula to restricts the data for one region. 

DAX Power BI
Image by Author

GROUP BY

One of the important aggregation function is GROUP BY. Syntax is as below

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

If you observe, GROUP BY and SUMMARIZE, both have same syntax. 

Then what is the difference between them? 

GROUP BY differs from SUMMARIZE and SUMMARIZECOLUMNS in the <expression> section arguments. 

GROUP BY only works with DAX iterator functions, so it uses SUMX instead of SUM, AVERAGEX rather than AVERAGE. For specific scenario, it works. 

Let’s find out one example.

GROUP BY DAX function in Power BI
Image by Author

From the above example, you can find another function CURRENTGROUP.

GROUP BY function introduces in DAX with a helper function called CURRENTGROUP() function which can be used instead of the original table.

GROUPBY WITH A FILTER

It’s time to add filter condition. You can add the same filter condition in the above function. Let’s find out from the example.

POWER BI DAX GROUP BY
Image by Author

Using CALCULATETABLE to Filter a GROUPBY

Another approach to use the GROUP BY function using the CALCULATETABLE function. You can check the below example for your better understanding.

DAX GROUP BY Power BI
Image by Author

Download

Please find the code in the below location

DAX — Chapter 4 https://github.com/arpitag1/Power-BI

Video

Conclusion

In this blog, the following things have been captured

  1. Purpose of Summarization
  2. SUMMARIZE function
  3. SUMMARIZATIONCOLUMNS function
  4. GROUP BY function

In my next blog, we will learn more about DAX.

If you have any questions related to this project, please feel free to post your comments.

Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.

4 comments

Leave a Reply to Thiru Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: