SUMX

Syntax

SUMX(<table>, <expression>)

Table of contents:

1. Explain the functions
2. Calculate the sum of distinct values
3. Calculate the sum of subcategories (or categories)
4. Sum child values into the parent

1. Explain the functions

In the video below,  we will go through: how to use SUM() and SUMX(), the difference between them and when should we use one vs the other.

The DAX SUM function calculates the sum of all numbers in a column while the DAX SUMX function returns the sum of an expression for each row in a table.

02:28 Create a measure to calculate the total sales using SUMX function
03:40 Create a calculated column for total sales (row by row)
05:16 Create a measure to calculate the total sales using SUM function
06:00 Difference between SUM and SUMX

Remarks

The SUMX function takes as its first argument a table, or an expression that returns a table. The second argument is a column that contains the numbers you want to sum, or an expression that evaluates to a column.

Only the numbers in the column are counted. Blanks, logical values, and text are ignored.

Example 2: Calculate the sum of distinct values in DAX

Have you ever tried to calculate the sum of distinct or unique values in Power BI? Not straight forward right?
A distinct sum is a sum where you specify the values that you want to have included in the totals. W
In this video tutorial we will go through the wrong and the right way(s) to do it.
Keynotes:
03:37 SUMX Calculation
06:00 Distinct sum with MAX as part of the expression
08:00 Distinct sum with MAX as a measure

Download example file:

To get the files:
1. Go to Curbal Download Center > Dax Fridays
2. Get File #12

Example 3: Calculate the sum of subcategories

Let me show you how to calculate the sum of subcategories.

To get the files:
1. Go to Curbal Download Center > Dax Fridays
2. Get File #57

Example 4: Sum child values into the parent

In today’s video, I will show you how to sum child values into the parent when you are dealing with hierarchies in Power BI.

To get the files:
1. Go to Curbal Download Center > Dax Fridays
2. Get File #91