How to Summarize Multiple Tables in DAX

In this article, you will learn how to summarize multiple tables using the summarize function in DAX.

Let’s get started.

Introduction

To summarize your tables, the first thing to do after launching your Power BI Desktop is to insert your data, then Transform Data, which takes you to Power Query Editor. Do not load your dataset directly.

In the Power Query Editor, you will have an overview of your dataset and see the tables you will be summarizing. Below is a view of my data.

You can download and use any Sample Superstore dataset to follow along.

In this data, I will be summarizing three columns which are the Category, Sub-Category, and Sales columns.

The purpose of summarizing the tables is to create a new table that only holds the total or summarized value (Total sales) for the Categories and Sub-Categories.

Now, let’s learn how to summarize these tables.

Create a new table

The first thing to do is to create a new table and to do that, you will close and apply to exit the Power Query Editor. Then, go to the Modeling tab and select New Table (as displayed below).

This will automatically create a new table which will be added to the Fields pane. And now you can create your DAX formula.

To create a new table, I used the formula below using three columns: Category, Sub-Category, and Sales.

Furniture_Summary = SUMMARIZE(Orders,Orders[Category],Orders[Sub-Category], Orders[Sales])

And now, the table has been created (as displayed below). However, we do not have a summarized table yet.

summarize multiple tables in dax

Create a New Measure

Create a new measure called Total_Sales using the formula below:

Total_Sales = SUM(Orders[Sales])

Summarize the Tables

Now, the next thing to do after creating the tables is to summarize them. To do that, I used the following formula:

Summary_Furniture = SUMMARIZE(Orders,Orders[Category],Orders[Sub-Category], "Sales_Amount",[Total_Sales])

I removed the Orders[Sales]) and added "Sales_Amount",[Total_Sales]).

NB: [Total_Sales] is a calculated measure.

Now, our tables have been summarized into Category, Sub-category, and Sales_Amount.

summarize multiple tables in dax

However, we only want to summarize the Furniture category. So, to do that, we will filter it out by using the following formula.

Summary_Furniture = SUMMARIZE(FILTER(Orders,Orders[Category]="Furniture"), Orders[Category],Orders[Sub-Category], "Sales_Amount",[Total_Sales])

And this is it. Our table has been summarized for furniture.

summarize multiple tables in dax

Conclusion

So, that is how you can summarize multiple tables in DAX using the SUMMARIZE and FILTER functions to summarize multiple tables in DAX.

I hope you found this article helpful.

Thanks for reading.

Leave a Comment