Summary Table Strategies: When to Opt for SUMMARIZE, GROUPBY, or SUMMARIZECOLUMNS

[technolog]

Consider a summary table using the SUMMARIZE function. If there will be only one column in the function, the SUMMARIZE function outputs one column with unique values.

 

As an alternative way of aggregation through the SUMMARIZE function, let's use the ADDCOLUMNS function.

In the first parameter of the ADDCOLUMNS function we specify the SUMMARIZE function, i.e. a table, and to this table we add the column to be aggregated. Specify its name, for example, ProductTotal, and then the code for calculating this column, inside which we embed the Total column from the Orders table

We see that the column with aggregation has appeared, but in each cell the total amount for all products has been calculated. We need to calculate the sum for each specific product.

The point is that in this construction, when calculating the aggregation column, the SUM function must be wrapped in the CALCULATE function to change the context or use a measure

 

We corrected the situation and got the desired summary table. However, you cannot create totals and summaries in such a summary table.

_______________________

Let's consider a situation where we can replace the SUMMARIZE function with the GROUPBY function.

Let's use the GROUPBY function, where in the first parameter we specify the table by which we will aggregate the values, in the second and the following parameters we can specify by which we need to group the values. The GROUPBY function is similar to the SUMMARIZE function except for a couple of things.

First, you cannot create totals and subtotals in the GROUPBY function by wrapping columns for grouping in the ROLLUP and ROLLUPGROUP functions.

Secondly, when creating aggregate columns, you cannot use simple aggregation functions, you will have to use iterative X-functions.

Therefore, let's replace SUM with SUMX in our function. In the first parameter we will specify the function CURRENTGROUP( )

 

The summary table is ready, but the name of the grouping column will consist of the name of the original Orders table and the original Product_ID column - 'Orders_Product ID'

_______________________

Let's consider one more variant of building summary tables - let's replace the SUMMARIZE function with the SUMMARIZECOLUMNS function. The peculiarity of this function is that now it is not necessary to specify the table in the first parameter. We leave only the parameter specifying the column by which we group the values. And we leave two parameters, by means of which the aggregation column is created

 

This method of producing summary tables is the fastest. However, SUMMARIZECOLUMNS is quite a complex function, you can add more parameters to it, as well as embed additional filters, you can create totals and sub-totals.

_______________________

Let's try to highlight the key points when choosing a function to create a summary table. If you need to create a summary table in DAX code, you can use the SUMMARIZE function, but you should remember that this function works quite slowly, so this function is suitable only if the initial table is small. Also, if you want to create totals and subtotals in the summary table, use the SUMMARIZE function. If you don't need totals and sub-totals and want to use the SUMMARIZE function, use it together with the ADDCOLUMNS function. The GROUPBY function is an excellent substitute for SUMMARIZE, but there is no possibility to create totals and sub-totals. If we want to create the most complex aggregation function, we can use SUMMARIZECOLUMNS.