Power BI SUMMARIZE

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Summarize Function in Power BI

The summary table is what end users would like to see from a large amount of data. With MS Excel users, we can use PivotTables to drag and drop the table fields 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. One such formula is the SUMMARIZE DAX function. This article will take you through this SUMMARIZE DAX function in detail with practical examples.

Table of contents

What Does SUMMARIZE Function Do in Power BI?

As the word itself says, SUMMARIZE will summarize the huge number of data rows into one table with a provided criteria column. For example, you may have multiple city sales values. Still, each city has multiple rows of transactions, so using the SUMMARIZE function, we can create a summary table where each city will have only one-row transactions with the summarized line.

Below is the syntax of the SUMMARIZE function in Power BI.

Power BI SUMMARIZE - Syntax
  • 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.

Power-BI-SUMMARIZE

Examples of SUMMARIZE Function in Power BI

Below are examples of the SUMMARIZE function in Power BI. You can download the workbook using the same file we used in this example.

Example #1

Below are examples of the SUMMARIZE function in Power BI.

  1. To demonstrate the SUMMARIZE DAX function, we will use the data table. You can download the Excel workbook to follow along with us.


    Power BI SUMMARIZE - Excel Data

  2. Upload the data table to the Power BI desktop file.


    Power BI SUMMARIZE - Table
    Everybody must remember that the "SUMMARIZE" function groups the data in a new table. Therefore, we can apply the SUMMARIZE function only when we create a new table, so it cannot be used as a "NEW COLUMN" or "NEW MEASURE."

  3. From the above data, we need to summarize the data based on "State," so go to the "Modeling" tab and choose the option of "New Table."


    Power BI SUMMARIZE - New Table

  4. It will ask you to name the table first, so name it "State Summary Table."


    Power BI SUMMARIZE - State Summary Table

  5. Now, open the Power BI SUMMARIZE function.


    Power BI SUMMARIZE function

  6. First, we need to mention the "Table" we are trying to summarize. So, in this case, the table we are summarizing is "Sales_Table," which is the same as the table name.


    Power BI SUMMARIZE - Sales Table

  7. We are trying to summarize this table based on the "State" column, so this will be our "GroupBy_ColumnName1."


    Power BI SUMMARIZE - State

  8. Once the column name is specified to summarize, we need to give a name to the new column, "Sales Value."


    Power BI SUMMARIZE - Sales Value

  9. "Expression 1" is nothing but how we need to summarize the mentioned column since we add sales values by state name. Open SUM function to add sales column values by each state.


    Power BI SUMMARIZE - Sum Function

  10. The column name we need to SUM is the "Sales" column, so mention the same.


    Power BI SUMMARIZE - Sales Condition

  11. That is it. Close the brackets and press the "Enter" key to get the summarized table based on state names.


    Power BI SUMMARIZE - Example1 Result
    As you can see, we have a new table together here. Thanks to the SUMMARIZE function.

Example #2

Imagine summarizing the data based on the "State" and the "Category" columns. 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."
Sales Table State Column
  • As of now, we have mentioned the "GroupBy_Column Name1" column. Now, mention the second-level group column name, i.e., the "Category" column.
Power BI SUMMARIZE - Category Column
  • Now, we have mentioned the two-level group by columns. Now, give a name for the new column as "Sales Value."
Sales Value Entry
  • Expression1 will add all the sales values, so use the SUM function to summarize the "Sale Value" column.
Power BI SUMMARIZE - Sum Function Formula
  • We are done. Close the bracket and press the "Enter" to get the new summarized table.
Example 2 Result

Here, we have the new SUMMARIZED table, which we have summarized based on "State" and "Category."

Note: We can also download Power BI SUMMARIZED function file from the link below and view the final output.

Things to Remember Here

  • We can only use the Power BI SUMMARIZE function to create a new table.
  • If there is only one level summarization, we can move to the "Name1" argument after mentioning the GroupBy column directly.
  • We can use all the aggregate functions in the Expression argument to decide the kind of summarization.