Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
koolsid786
Frequent Visitor

When to use Sum and Sumx

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 

1 ACCEPTED SOLUTION
AjithPrasath
Resolver II
Resolver II

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.

View solution in original post

6 REPLIES 6
AjithPrasath
Resolver II
Resolver II

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.

AjithPrasath
Resolver II
Resolver II

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.

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 ? 


koolsid786_0-1684628599322.png

 

koolsid786_1-1684628633747.png

 

 

tamerj1
Super User
Super User

Hi @koolsid786 

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

a3t
Frequent Visitor

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

 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 ? 


koolsid786_2-1684628761639.png

 

 

koolsid786_3-1684628761868.png

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors