Dataset used in this project is SlideScope Retails Sales Data, that you can get from here: https://slidescope.in/digital-store/retail-store-sales-dataset-for-ai-ml-and-data-analytics-2026/

Here is a snapshot of the data:

DAX (Data Analysis Expressions) provides powerful functions in Power BI to calculate, analyse, and summarise business data. Some functions perform calculations across individual rows, while others count records or evaluate values based on specific conditions. In this tutorial, we will explore five useful DAX functions using our retail sales dataset, where the table name is data. We will understand SUMX, AVERAGEX, COUNTAX, COUNTROWS, and MAXX through practical examples. These functions help analysts calculate total sales, average transaction values, count records, and identify the highest sales amounts, making business reporting more effective and insightful.

1. SUMX() – Calculate Sum Using Row-by-Row Expressions

SUMX evaluates an expression for every row in a table and then adds all the calculated results.

DAX Code:

Total Calculated Sales =
SUMX(
    data,
    data[Unit_Price] * data[Items_Sold]
)

Explanation:

• data specifies the table.
• data[Unit_Price] retrieves the product price.
• data[Items_Sold] retrieves the quantity sold.
• SUMX multiplies price by quantity for every row and adds the results.

Practical Use: Calculate total sales when the required value needs to be calculated from multiple columns.

2. AVERAGEX() – Calculate Average Using Expressions

AVERAGEX evaluates an expression for each row and calculates the average of all resulting values.

DAX Code:

Average Sales Per Transaction =
AVERAGEX(
    data,
    data[Net_Sales_Amount]
)

Explanation:

• data specifies the table.
• data[Net_Sales_Amount] provides the net sales value.
• AVERAGEX calculates the average net sales across the rows.

Practical Use: Find the average sales value per transaction to understand typical customer spending.

3. COUNTAX() – Count Non-Blank Values Using Expressions

COUNTAX evaluates an expression for every row and counts the results that are not blank.

DAX Code:

Total Customer Types =
COUNTAX(
    data,
    data[Customer_Type]
)

Explanation:

• data specifies the table.
• data[Customer_Type] contains customer classifications.
• COUNTAX counts all non-blank customer type values.

Practical Use: Count transactions that contain a customer classification, including New, Member, and Guest.

4. COUNTROWS() – Count Total Number of Rows

COUNTROWS counts the total number of rows present in a specified table or table expression.

DAX Code:

Total Transactions =
COUNTROWS(data)

Explanation:

• data represents the retail sales table.
• COUNTROWS counts every row in the table.
• Each row represents one transaction in our dataset.

Practical Use: Calculate the total number of transactions processed by the retail store.

5. MAXX() – Find Maximum Value Using Expressions

MAXX evaluates an expression for every row and returns the highest calculated value.

DAX Code:

Highest Transaction Value =
MAXX(
    data,
    data[Net_Sales_Amount]
)

Explanation:

• data specifies the table.
• data[Net_Sales_Amount] contains the transaction values.
• MAXX evaluates the values and returns the highest one.

Practical Use: Identify the highest-value transaction to understand the store’s biggest individual sales.

Conclusion

DAX iterator and counting functions are essential tools for performing calculations in Power BI. SUMX helps calculate totals using row-level expressions, while AVERAGEX determines averages across records. COUNTAX counts non-blank expression results, COUNTROWS identifies the total number of records, and MAXX finds the highest calculated value. These five functions provide practical solutions for analysing retail sales, customer transactions, and business performance. Understanding how these functions work will help you create more meaningful measures and interactive dashboards. Instead of relying only on basic aggregations, learning iterator functions allows Power BI developers to perform flexible calculations and build more advanced business intelligence reports.