Power BI Summarize

What is SUMMARIZE Function in Power BI?

The SUMMARIZE function groups rows from a specified table based on one or more grouping columns and then calculates aggregates on the specified columns within those groups. It’s a powerful feature that helps you analyze and present data more structuredly and meaningfully. It creates a new table that combines the unique combinations of the grouping columns with the calculated aggregated values. It is extremely useful for creating summary reports and dashboards that provide insights into the data’s trends and patterns.

What does SUMMARIZE Function do in Power BI?

The SUMMARIZE function groups rows from a specified table based on one or more grouping columns and then calculates aggregates on the specified columns within those groups. It creates a new table that combines the unique combinations of the grouping columns with the calculated aggregated values. This is extremely useful for creating summary reports and dashboards that provide insights into the data’s trends and patterns.

Syntax

The syntax of the Power BI SUMMARIZE function is:

SUMMARIZE(Table, [GroupBy_ColumnName1], …, [Name1], [Expression1])

• Table – Any DAX expression that returns a data table. It can be a physical table, an expression, a filtered dataset, etc.
• GroupBy_ColumnName1 – This is an optional argument and refers to the existing column name for creating a summary group based on the underlying values. It should not be an expression.
• Name1 – the name of the summarized column. It must be defined within double quotes.
• Expression1 – th DAX expression to be evaluated. It returns a single scalar value.

How to Use SUMMARIZE Function in Power BI?

Here’s how you can use the SUMMARIZE function in Power BI:

Step 1: Open your Power BI Desktop. Load your dataset using the Get data option.

Step 2: Navigate to the Modeling tab. Choose the New table option.

Step 3: Enter the Power BI SUMMARIZE DAX in the formula bar and then click on Commit to save the changes.

A sample Power BI SUMMARIZE by month DAX example is shown below. Here we have applied Power BI SUMMARIZE with filter to create a Power BI SUMMARIZE table SUMMARIZE Sales Data.

SUMMARIZE Sales Data = SUMMARIZE(FILTER(financials,financials[Year] = 2013), financials[Month Name],financials[Segment],financials[Product], “Total Sales”, SUM(financials[Gross Sales]), “Total Profits”, SUM(financials[Profit]), “Total Discounts”, SUM(financials[Discounts]))

Step 4: Use the newly created table to create data visualization in Power BI.

In the subsequent sections, we will see how to use the SUMMARIZE function in Power BI through some examples.

Examples of SUMMARIZE Function in Power BI

In this section, we will demonstrate 2 examples to show the Power BI SUMMARIZE table and Power BI SUMMARIZE columns using SUMMARIZE function in Power BI.

Example #1

In this example, we will create a Power BI SUMMARIZE table of country wise statistics using the Global Statistics Time Series dataset. The Global Statistics Time Series dataset contains the country wise statistics from 2000-2015 time series data.

To use the Power BI SUMMARIZE function, follow the steps highlighted below:

Step 1: Open the Power BI Desktop, import the Global Statistics Time Series dataset using the Get Data option, and click on the Load button. You can choose the Transform Data option to perform data transformation as needed.

Once the data is loaded, you shall be able to view it by navigating to the Fields pane.

Step 2: Navigate to the Modeling tab and choose the New table option.

Once you choose the New table option, you will see a prompt in the formula bar.

Step 3: Enter the DAX expressions in the formula bar to create the Power BI SUMMARIZE table on the Global Statistics Time Series table.

Here we have created a Power BI SUMMARIZE table SUMMARIZE Global Stats By Country and Year to summarize the country wise stats across 2000-15 time series data.

SUMMARIZE Global Stats By Country and Year = SUMMARIZE(‘Global Statistics Time Series’ ROLLUP(ROLLUPGROUP(‘Global Statistics Time Series'[Country])), “Total GDP”,SUM(‘Global Statistics Time Series'[GDP]), “Total Expenditure”, SUM(‘Global Statistics Time Series'[Total expenditure]), “Total Population”, SUM(‘Global Statistics Time Series'[Population]), “Average Life Expectancy”, AVERAGE(‘Global Statistics Time Series'[Life expectancy]), “Average BMI”, AVERAGE(‘Global Statistics Time Series'[BMI]))

Notice that we have used ROLLUP and ROLLUPGROUP functions to add the grand total for each country.

Click on the Commit icon to save the changes. It shall create a Power BI SUMMARIZE table.

You can see the underlying values for this new table by navigating to the Data view tab.

Step 4: Navigate to Visualizations pane, choose the Table visual, and drag and drop the data fields from the Fields pane into the columns section of the visual.

This will create a table visual with the Power BI SUMMARIZE table dataset.

Example #2

In this example, we will create a Power BI SUMMARIZE table of Data Science salary details between 2021 – 23 using the Data Science Salary 2021-23 dataset. The Data Science Salary 2021-23 dataset contains the salary ranges across various Data Science streams based on experience, company location, company size, and other details from 2021-2023 data.

To use the Power BI SUMMARIZE function, follow the steps highlighted below:

Step 1: Open the Power BI Desktop, import the Data Science Salary 2021-23 dataset using the Get Data option, and click the Load button. You can choose the Transform Data option to perform data transformation as needed.

Once the data is loaded, you shall be able to view it by navigating to the Fields pane.

Step 2: Navigate to the Modeling tab and choose the New table option.

Once you choose the New table option, you will see a prompt in the formula bar.

Step 3: Enter the DAX expressions in the formula bar to create the Power BI SUMMARIZE table on the Data Science Salary 2021-23 table.

Here we have created a Power BI SUMMARIZE table Data Science Salary Summarized to store aggregated salary information across various parameters.

Data Science Salary Summarized = SUMMARIZE(‘Data Science Salary 2021-23’, ROLLUP(ROLLUPGROUP(‘Data Science Salary 2021-23′[work_year], ‘Data Science Salary 2021-23′[company_location], ‘Data Science Salary 2021-23′[job_title],

 ‘Data Science Salary 2021-23′[experience_level], ‘Data Science Salary 2021-23′[employment_type], ‘Data Science Salary 2021-23′[company_size] )), “Salary in USD”,SUM(‘Data Science Salary 2021-23′[salary_in_usd]))

Click on the Commit icon to save the changes. It shall create a Power BI SUMMARIZE table by using the Power BI SUMMARIZE columns in the Data Science Salary 2021-23 dataset.

You can see the underlying values for this new table by navigating to the Data view tab.

Step 4: Navigate to the Visualizations pane, choose the Matrix visual, and drag and drop the data fields from the Fields pane into the columns section of the visual.

Step 5: Navigate to the Format your visual tab under the Visualizations pane and apply formatting options to your visual.

It will create a Matrix visual with the Power BI SUMMARIZE table dataset.

Important Things to Note

• The SUMMARIZE function creates a table with grouped results. It doesn’t directly modify your existing data table but generates a new table.
• You must specify an expression for every column in the SUMMARIZE function for which you define a name. Otherwise, Power BI will return an error. The name defined in the SUMMARIZE function should be enclosed in double quotation marks.
• The SUMMARIZE function can be memory-intensive, especially for large datasets. Use it judiciously and consider using filters or other optimization techniques to improve performance.
• Be careful with the number of grouping and aggregation columns you include, as this can quickly lead to a proliferation of rows in the resulting summary table.
• Understand the impact of the filter context in your Power BI report, as it can affect the outcome of the summary.
• Remember that the SUMMARIZE function returns a table, so it’s often used as part of other DAX calculations.
• You can’t use the SUMMARIZE function for use in DirectQuery mode for use in calculated columns or row-level security rules.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to Power BI SUMMARIZE. Here we look how to use Summarize function in Power BI with examples and its syntax. You can learn more from the following articles –

SUMX in Power BI

Power BI ALL