Power BI

Understanding the SUMMARIZE() function In DAX

In our Power BI DAX Essentials course, the instructors focus on coaching Power BI DAX skills based on applicable examples. One of such skills is creating summary tables over the requested totals. SUMMARIZE() is a DAX function commonly used for this purpose.Formula

In the last blog, we used SUMMARIZE() to create summary tables over the requested totals. In this blog you will be introduced to some more use-cases of the SUMMARIZE() function.

 Adding columns from other data tables:

You can also add columns from other tables but this is only possible when the input table is on the many side of the relationship. Let’s see how this can be done.

  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.Summarize-function

In this Expression:

  • The Sales table is grouped by the product Category, Subcategory, Country, 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.
  1. Click on the Data tab in the left Ribbon.
  2. Click on Testing Table Functions using SUMMARIZE – Expression or Measure (CT) in the Fields pan.Fields pan

Please note that you might get a different order of columns when you try to add the Country column from Sales Territory in an existing table. You can fix this by deleting the original table and recreating it with same expression.

Using SUMMARIZE() to get data for all categories:

If you use Product table as an input table and get category and Subcategory columns over the requested totals, you will get all Subcategories. Let’s see how this works:

  1. Click on the ‘Testing Table Functions using SUMMARIZE(CT)’ in Fields.
  2. Type the following expression in the formula bar and press Enter.Testing Table Functions

In this expression:

  • you have replaced the first argument from ‘Sales’ to ‘Product’.
  • Added a column for Product Subcategory as the last argument of SUMMARIZE().
  1. Click on the Data tab in the left Ribbon.
  2. Click on Testing Table Functions using SUMMARIZE (CT) in the Fields pan.Testing Table Functions -category
  3. Scroll to the end to this table.Testing Table Functions -category-table

Notice that the table contains 37 rows, which means all the sub categories are present. Now let’s add totals from the Sales column.

Now lets try adding measures.

  1. Right click on All Measures in the Fields.
  2. Click on New measure.

Measure tools open.

  1. Type the following formula in the formula bar and press Enter.Total-sale

In this expression:

  • The total sales amount is calculated by summation of Sales Amount values in the Sales table.
  1. Click on the Testing Table Functions using SUMMARIZE (CT) in the Fields pan.

Table tools box opens.

  1. Add “Sales”, [Total Sales Amount (CM)] as the last argument of SUMMARIZE().Summarize-table-2

Your expression should look similar to the one below.

In this Expression:

  • The Product table is grouped by the product Category, Subcategory and Sales Amount.
  • The “Sales” column stores the result of the measure for each row of the new table.
  1. Click on Sales column in the Testing Table Functions using SUMMARIZE (CT) in the Fields pan.

Column tools box opens.

  1. Click on the $ symbol and write 2 in the text box to set formatting.formatting
  2. Click on the Data tab in the left Ribbon.
  3. Click on Testing Table Functions using SUMMARIZE (CT) in the Fields pan.
  4. Click on the drop down on the Sales
  5. Click on Sort Ascending.Sort Ascending

Notice two subcategories where there is no sale in Sales Table. This shows that if input table is on one side of the relationship, you cannot fetch Columns from other datasets.

SUMMARIZE() function (unlike ALL) does not ignore the filter context.

Conclusion:

SUMMARIZE() is a commonly used DAX function for creating summary tables. The resulting summary table depends on the input arguments in the function. Sometimes, it is important to fetch data from other tables to meet the requirement of a calculation. In the first part of this blog post, a step-by-step guide to adding columns from other tables is presented. In the second half of the blog, SUMMARIZE() has been used to get data for all categories.

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

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *