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.

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.

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.

4. Click on the Data tab in the left Ribbon.
5. Click on Testing Table Functions using SUMMARIZE – Expression or Measure (CT) in the 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:

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

In this expression:

• you have replaced the first argument from ‘Sales’ to ‘Product’.
• Added a column for Product Subcategroy as the last argument of SUMMARIZE().

8. Click on the Data tab in the left Ribbon.
9. Click on Testing Table Functions using SUMMARIZE (CT) in the Fields pan.
10. Scroll to the end to this 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.

11. Right click on All Measures in the Fields.
12. Click on New measure.

Measure tools open.

13. Type the following formula in the formula bar and press Enter.

In this expression:

• The total sales amount is calculated by summation of Sales Amount values in the Sales table.

14. Click on the Testing Table Functions using SUMMARIZE (CT) in the Fields pan.

Table tools box opens.

15. Add “Sales”, [Total Sales Amount (CM)] as the last argument of SUMMARIZE().

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.

16. Click on Sales column in the Testing Table Functions using SUMMARIZE (CT) in the Fields pan.

Column tools box opens.

17. Click on the $ symbol and write 2 in the text box to set formatting.
18. Click on the Data tab in the left Ribbon.
19. Click on Testing Table Functions using SUMMARIZE (CT) in the Fields pan.
20. Click on the drop down on the Sales
21. Click on 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