Summary Table Strategies: When to Opt for SUMMARIZ…

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.

Screenshot 2023-09-03 at 18.28.09.png

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

Screenshot 2023-09-03 at 18.29.16.png

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

Screenshot 2023-09-03 at 18.29.49.png

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( )

Screenshot 2023-09-03 at 18.30.47.png

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

Screenshot 2023-09-03 at 18.31.30.png

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.

Source link

Be the first to comment

Leave a Reply

Your email address will not be published.