Power BI SUMMARIZE

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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 formulasPower BI Is DAX FormulasIn Power Bi, DAX stands for Data Analysis and Expression and is a functional language that represents all of the functions in Excel and Power BI. Formulas in Power BI can be created using the DAX language.read more. One such formula is the SUMMARIZE DAX function. This article will take you through this SUMMARIZE DAX function in detail with practical examples.

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 BIParameters Of The Power BIA parameter means having different kinds of scenarios to look at the final outcome of any of the project. Using the parameter slicer in Power BI, you can view different results at different inputs.read more SUMMARIZE function.

Power-BI-SUMMARIZE

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI SUMMARIZE (wallstreetmojo.com)

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.

You can download this Power BI SUMMARIZE Function Excel Template here – Power BI SUMMARIZE Function Excel Template

–>> If you want to learn Power BI professionally, then Power BI Basics Course (5+ hours) is the perfect solution. This course will introduce you to a wide range of Power BI concepts like collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights, Creating Reports in Power BI Desktop, Analyze Data with Excel, Graphs & Visualizations, Interactive Dashboards along with some real life examples.

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.

You can download this Power BI SUMMARIZE Function Template here – Power BI SUMMARIZE Function Template

Things to Remember Here

This article has been a guide to Power BI SUMMARIZE. Here, we discuss using the 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: –