Evaluating Sales and Calculating Total Sales and Average Sales in DAX

September 8, 2026 21 visits

In this article, we will learn how to retrieve the complete
Sales table and calculate Total Sales
and Average Sales using EVALUATE,
ROW, SUM, and AVERAGE
in DAX.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist

1. Retrieving the Complete Sales Table

Query

DAX Query
EVALUATE
    Sales

Step-by-step explanation

Step 1: EVALUATE
EVALUATE is used to return the result of a table expression.
A DAX query must return a table.

Step 2: Sales
Sales is the table expression. Since we are directly
referencing an existing table, DAX returns the complete table.

What will you get?

The result contains all columns and rows available in the
Sales table.

SalesTerritoryKey ResellerKey ProductKey Sales Amount
1 101 1001 500
2 102 1002 750
3 103 1003 1,250


The example above is only for illustration.
Your actual result will contain the data available in your Sales table.

Key takeaway:
EVALUATE Sales returns the complete Sales table.

2. Calculating Total Sales and Average Sales

Query

DAX Query
EVALUATE
ROW(
    "Total Sales", SUM(Sales[Sales Amount]),
    "Average Sales", AVERAGE(Sales[Sales Amount])
    )

Step-by-step explanation

Step 1: EVALUATE
EVALUATE tells DAX to return the result of the
table expression.

Step 2: ROW()
ROW creates a table containing a single row.
Inside ROW, we define the column names and the
expressions used to calculate their values.

Step 3: “Total Sales”
This is the name of the first output column.

Step 4: SUM(Sales[Sales Amount])
SUM adds all the values in the
Sales[Sales Amount] column.
The result is the total sales amount.

Step 5: “Average Sales”
This is the name of the second output column.

Step 6: AVERAGE(Sales[Sales Amount])
AVERAGE calculates the arithmetic mean of the values
in the Sales[Sales Amount] column.

Understanding the ROW syntax

ROW Syntax
ROW(
    "Column Name 1", Expression 1,
    "Column Name 2", Expression 2
)

Each pair contains an output column name followed by an expression
that calculates its value.

What will you get?

The query returns a table containing one row and two columns.

Total Sales Average Sales
2,500 833.33


The values above are illustrative. Your actual results depend on
the values in your Sales table.

Key takeaway:
ROW allows you to return multiple calculated values
as columns in a single-row table.

3. Understanding SUM and AVERAGE

Function Purpose Example
SUM Adds all values in a column SUM(Sales[Sales Amount])
AVERAGE Calculates the arithmetic mean of a column AVERAGE(Sales[Sales Amount])

How SUM works

Suppose the Sales Amount column contains the following values:

Sales Amount
500
750
1,250

The total sales is:

SUM Calculation
500 + 750 + 1,250 = 2,500

How AVERAGE works

The average sales is calculated by dividing the total of the values
by the number of values.

AVERAGE Calculation
(500 + 750 + 1,250) / 3 = 833.33

Therefore, SUM returns the total, while
AVERAGE returns the average value.

4. Understanding the Difference Between the Two Queries

Query Result
EVALUATE Sales Returns the complete Sales table
EVALUATE ROW(…) Returns a single-row table containing calculated values
Key takeaway:
The first query retrieves data, while the second query calculates
and returns summary values.

5. Important Notes

1. EVALUATE returns a table

Even though the second query returns only two calculated values,
the result is still a table.

2. ROW returns a single row

ROW is useful when you want to return multiple
calculated values in one result.

3. SUM and AVERAGE operate on a column

In this example, both functions use the
Sales[Sales Amount] column.

4. Output column names are customizable

The names “Total Sales” and
“Average Sales” are the names of the output columns.

5. The original Sales table is not modified

The second query creates a table expression for the result.
It does not change the original Sales table.

📝 Practice Exercise

Try writing a query to calculate the following values from the
Sales table:

  • Total Sales Amount
  • Average Sales Amount

Rename the output columns as “Total Sales” and
“Average Sales”.

Expected Query

DAX Query
EVALUATE
ROW(
    "Total Sales", SUM(Sales[Sales Amount]),
    "Average Sales", AVERAGE(Sales[Sales Amount])
    )

Conclusion

In this article, we learned how to retrieve the complete Sales table
and calculate summary values using DAX.

  • EVALUATE Sales returns the complete Sales table.
  • ROW creates a single-row table.
  • SUM calculates the total of a column.
  • AVERAGE calculates the arithmetic mean of a column.
  • We can return multiple calculated values using ROW.

These are the fundamental building blocks for writing DAX queries
and exploring data in tools such as DAX Studio.

Next: We can learn how to sort tables using
ORDER BY and retrieve specific rows using
START AT.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist