Summary tables are a part of our every Power BI DAX training because they provide a bird-eye’s look at the data. They are created using DAX functions such as SUMMARIZE() which groups the data over a single or multiple columns or over a measure or a DAX expression. The resulting table depends on the input arguments used in the SUMMARIZE() which in turn is based on the requirement. This blog post presents the three methods for creating summary table for the requested totals.

SUMMARIZE() allows us to create a summary of the input table grouped by the specified columns.

Totals over a Group-by column:

Let’s create a summary table where the input table is Sales while category is coming from Product Table. To use SUMMARIZE() for this:

1. Click on the Modeling tab in the Ribbon.
2. Click on New Table.
3. Type the following expression in the formula bar and press Enter.

In this Expression:

• The Sales table is grouped by the product Category.

4. Click on the Data tab in the left Ribbon.
5. Click on Testing ‘Table Functions using SUMMARIZE (CT)’ in the Fields pan.

Totals over a multiple Group-by columns:

6. Click on the Modeling tab in the Ribbon.
7. Click on New Table.
8. Type the following expression in the formula bar and press Enter.

In this Expression:

• The Sales table is grouped by the Product Category and Subcategory.

9. Click on the Data tab in the left Ribbon.
10. Click on Testing ‘Table Functions using SUMMARIZE – 2 col (CT)’ in the Fields pan.
11. Scroll down to the end of table.

The table statistics show that 35 rows exist in this table. however, we have 37 Sub-categories. This means 2 Subcategories for which we have no sales does not appear here.

This happens because input table of the function is Sales and it only brings those categories and subcategories where we have a sale (at least 1 row in sales table)

SUMMARIZE() using measure or expression for GroupBy:

12. Click on the Modeling tab in the Ribbon.
13. Click on New Table.
14. Type the following expression in the formula bar and press Enter.

In this Expression:

• The Sales table is grouped by the product Category, Subcategory, Sales Amount and Total Product Cost.
• The “Sales” column stores the aggregated value of Sales Amount for each row of the new table.
• The “Total Cost” column stores the aggregated value of Total Product Cost for each row of the new table.

15. Click on the Sales Column in Testing Table Functions SUMMARIZE – Expression or Measure (CT).

Column tools open.

16. Click on the $ symbol and write 2 in the text box to set formatting.
17. Click on the Total Cost Column in Testing Table Functions SUMMARIZE – Expression or Measure (CT).

Column tools open.

18. Click on the $ symbol and write 2 in the text box to set formatting.
19. Click on the Data tab in the left Ribbon.
20. Click on Testing Table Functions using SUMMARIZE – Expression or Measure (CT) in the Fields pan.

Conclusion:

SUMMARIZE() function in DAX is used to a create summary table of the requested totals over single or multiple Group-by columns as well as over values calculated by measures or expressions. The summary table shows summarized information about the data columns used in the DAX expression. The result of the expression depends on the arguments used. If a single column is used as an argument in the SUMMARIZE() function then only the unique values in the column are returned. Similarly, if two columns are used as argument in SUMMARIZE() then a unique combination of the values in the both the columns is returned. For measures and expressions, an aggregated value over the categories is returned. This blog presents an example for each of the above cases.    

Are you a data analyst and want to learn more about Power BI?

Why not sign up for Power BI training in Australia. We provide our services in the following regions:
• Power BI Training in Sydney
• Power BI Training in Melbourne
• Power BI Training in Brisbane