- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Engagement
- T-Shirt Design Challenge 2023
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- When to use Sum and Sumx

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

When to use Sum and Sumx

05-18-2023
06:39 PM

Hello All,

I have read so many articles but not able to understand when someone should use Sum and SumX , Average and AverageX.

Can someone explain with basic example ?

Thanks

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-21-2023
10:52 PM

Hi @koolsid786

**SUM function:** The SUM function calculates the sum of a specified column in a table. It takes a column or an expression that evaluates to a column as an argument.

**SUM(<column>)**

**Example: **Consider a table called "Sales" with columns "Product" and "SalesAmount." To calculate the total sales amount, you can use the SUM function as follows:

**TotalSalesAmount = SUM(Sales[SalesAmount])**

**SUMX function:** The SUMX function calculates the sum of an expression for each row in a table or an iterator. It iterates over each row and performs the specified calculation on the expression.

**SUMX(<table>, <expression>)**

**Example: **Suppose you have a table called "Orders" with columns "Product," "Quantity," and "Price." To calculate the total revenue for each product (quantity multiplied by price), you can use the SUMX function as follows:

**TotalRevenue = SUMX(Orders, Orders[Quantity] * Orders[Price])**

This will iterate over each row in the "Orders" table, calculate the revenue for each row (Quantity * Price), and then sum up the results to provide the total revenue.

Best Regards,

*Ajith Prasath*

If this post helps, then please consider **Accept it as the solution** to help the other members find it more quickly.

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-21-2023
10:52 PM

Hi @koolsid786

**SUM function:** The SUM function calculates the sum of a specified column in a table. It takes a column or an expression that evaluates to a column as an argument.

**SUM(<column>)**

**Example: **Consider a table called "Sales" with columns "Product" and "SalesAmount." To calculate the total sales amount, you can use the SUM function as follows:

**TotalSalesAmount = SUM(Sales[SalesAmount])**

**SUMX function:** The SUMX function calculates the sum of an expression for each row in a table or an iterator. It iterates over each row and performs the specified calculation on the expression.

**SUMX(<table>, <expression>)**

**Example: **Suppose you have a table called "Orders" with columns "Product," "Quantity," and "Price." To calculate the total revenue for each product (quantity multiplied by price), you can use the SUMX function as follows:

**TotalRevenue = SUMX(Orders, Orders[Quantity] * Orders[Price])**

This will iterate over each row in the "Orders" table, calculate the revenue for each row (Quantity * Price), and then sum up the results to provide the total revenue.

Best Regards,

*Ajith Prasath*

If this post helps, then please consider **Accept it as the solution** to help the other members find it more quickly.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-18-2023
09:35 PM

SUM vs. SUMX:

- SUM: The SUM function is an aggregation function that calculates the sum of a column or expression within a single table or filter context. It works on a column and returns the sum of all the values in that column.
- SUMX: The SUMX function is an iterator function that allows you to perform a calculation (such as summing values) for each row of a table or an expression within a specific context. It works on an iterator table and applies the specified calculation to each row, and then aggregates the results.
- AVERAGE vs. AVERAGEX:
- AVERAGE: The AVERAGE function is an aggregation function that calculates the average of a column or expression within a single table or filter context. It works on a column and returns the average value of all the values in that column.
- AVERAGEX: The AVERAGEX function is an iterator function that allows you to perform a calculation (such as averaging values) for each row of a table or an expression within a specific context. It works on an iterator table and applies the specified calculation to each row, and then aggregates the results.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-20-2023
05:24 PM

Hi Ajith,

Can you please explain with Example ?

Additionally I am facing dificultiies in generating Dax formulas how to practice those ?

For Ex. In the below screen shot I have used Distinct but the Asnwer is showing that we should use Summarize ? Which function to be used when how to understand those ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-18-2023
07:48 PM

Hi @koolsid786

you can use the x aggregators when you need to get access to row context otherwise just use normal SUM, AVERAGE etc.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-18-2023
06:49 PM

This reference guide gives some good examples: https://dax.guide/sumx/

First examples shows similarities:

-- SUM is the short version of SUMX, when used with one column only

-- SUMX is required to evaluate formulas, instead of columns

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-20-2023
05:26 PM

I am facing dificultiies in generating Dax formulas how to practice those ?

For Ex. In the below screen shot I have used Distinct but the Asnwer is showing that we should use Summarize ? Which function to be used when how to understand those ?

Announcements

Check out the November 2023 Power BI update to learn about new features.

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.