Summarize Function in Power BI
Summary Table is what end users would like to see from a large amount of data. With MS Excel users we can simply use pivot tables to drag and drop fields of the table to get the summary table.With Power BI, we can use visuals to get the summary table or visual, but one thing you need to learn in Power BI is DAX formulas and one such formula is “SUMMARIZE” DAX function. In this article, we will take you through this SUMMARIZE DAX function in detail with practical examples.
What Does SUMMARIZE Function Do in Power BI?
SUMMARIZE as the word itself says it will summarize the huge amount of rows of data into one table with provided criteria column. For example, you may have multiple city sales values but each city has multiple rows of transactions, so using SUMMARIZE function we can create a summary table where each city will have an only one-row transaction with the summarized line.
Below is the syntax of the SUMMARIZE function in Power BI.
- Table Name: First we need to mention the table name that we are summarizing.
- Group by Column Name1: From the Table, we need to mention what is the column we are summarizing.
- Group by Column Name2: From the Table, we need to mention what is the second column we are summarizing.
- Name 1: What is the name for the summarizing column?
- Expression 1: What is the kind of summarization you want to do?? Whether you want to sum, whether you want to take the average of the column or some other kind of calculation.
- Name 2: What is the name for the second summarizing column?
- Expression 2: What is the kind of summarization you want to do for the second column??
These are parameters of the power BI SUMMARIZE function.
Examples of SUMMARIZE Function in Power BI
Below are examples of the SUMMARIZE function in Power BI. You can download the workbook to use the same file as we used in this example.
- To demonstrate the SUMMARIZE DAX function we are going to use below data table, you can download the Excel workbook to follow along with us.
- Upload the data table to the Power BI desktop file.
One thing everybody needs to remember here is the “SUMMARIZE” function is used to group the data in a new table, We can apply SUMMARIZE function only when we create a new table, so cannot be used as NEW COLUMN or NEW MEASURE.
- From the above data we need to summarize the data based on “State-wise”, so go to the MODELLING tab and choose the option of “New Table”.
- This will ask you to name the table first, so name it as “State Summary Table”.
- Now open Power BI SUMMARIZE function.
- First we need to mention the Table that we are trying to summarize, so in this case, the table that we are summarizing is “Sales_Table”, mention the same as the table name.
- In this mentioned table we are trying to summarize based on the “State” column, so this will be our Group by Column Name1.
- Once the column name specified to summarize, we need to give a name to the new column, give name as “Sales Value”.
- Expression 1 is nothing but how we need to summarize the mentioned column since we are adding sales values by state name open SUM function to add sales column values by each state.
- Colum Name that we need to SUM is the “Sales” column, so mention the same column.
- Ok, that’s it closes the brackets and hit enter key to get the summarized table based on state names.
As you can see we have a new table all together here, says thanks to SUMMARIZE function.
Now imagine a situation of summarizing the data based on the “State” column as well as the “Category” column, so this is where we need to use multiple conditions of SUMMARIZE function.
- First, mention the SUMMARIZE function in the new table and choose the column to be summarized and the first group by column as “State”.
- As of now, we have mentioned the first group by column, now mention the second-level group column name i.e. “Category” column.
- Now we have mentioned the two-level group by columns, now give a name for the new column as “Sales Value”.
- Expression1 will be we are adding up all the sales values so use the SUM function to summarize the sale value column.
- Ok, we are done close the bracket and hit enter to get the new summarized table.
Here we have the new SUMMARIZED table, we have summarized based on “State” and “Category”.
Note: Power BI SUMMARIZED function file can also be downloaded from the link below and the final output can be viewed.
Things to Remember Here
- Power BI SUMMARIZE function can be used to create a new table only.
- If there is only one level summarization then after mentioning the group by column directly we can move to the “Name1” argument.
- We can use all the aggregate functions in the Expression argument to decide the kind of summarization.
This has been a guide to Power BI SUMMARIZE. Here we discuss how to use SUMMARIZE function in power BI to summarize a large amount of data into one table along with examples. You may learn more about Power BI from the following articles –