AXEL Aggregation Functions

📊 Basic Aggregation

💡 Note: AXEL syntax is similar to DAX in Power BI. Functions are in UPPERCASE, table names are in [brackets], and expressions follow a functional programming style. Functions

Master these 13 essential functions for summarizing and analyzing your data.

SUM

Calculate the total sum of numeric values in a column.

SUM([Monthly Report].First_Class_Mail)

Use Cases: Total sales, revenue sums, quantity totals
Returns: Single numeric value

AVG

Calculate the arithmetic mean (average) of numeric values.

AVG([Monthly Report].Return_Mail)

Use Cases: Average order value, mean temperatures, typical values
Returns: Single numeric value

COUNT

Count non-blank values in a column.

COUNT([Monthly Report].Month)

Note: Excludes NULL and blank values
Use Cases: Valid entries, completed records, response counts

COUNTA

Count all values including blanks.

COUNTA([Monthly Report].Month)

Note: Includes blank values in count
Use Cases: Total row counts, all records including incomplete

MIN

Find the minimum (smallest) value.

MIN([Monthly Report].First_Class_Mail)

Use Cases: Lowest price, earliest date, minimum threshold
Returns: Single value of same type as column

MAX

Find the maximum (largest) value.

MAX([Monthly Report].First_Class_Mail)

Use Cases: Highest score, latest date, peak values
Returns: Single value of same type as column

DISTINCTCOUNT

Count unique/distinct values.

DISTINCTCOUNT([Monthly Report].Year)

Use Cases: Unique customers, distinct products, variety metrics
Returns: Integer count of unique values

🔄 Iterator Functions (X-Variants)

These functions evaluate expressions row-by-row before aggregating.

SUMX

Sum with row-by-row evaluation.

SUMX([Monthly Report], [Return_Mail] * 2)

Power: Apply calculations before summing
Use Cases: Weighted sums, calculated totals

AVGX

Average with row-by-row evaluation.

AVGX([Monthly Report], [First_Class_Mail] / 100)

Power: Transform values before averaging
Use Cases: Weighted averages, normalized metrics

COUNTX

Count rows with custom evaluation.

COUNTX([Monthly Report], 1)

Power: Conditional counting with expressions
Use Cases: Filtered counts, complex criteria

COUNTROWS

Count all rows in a table (Power BI compatible).

COUNTROWS([Monthly Report])

Simplicity: Direct row count without conditions
Use Cases: Table sizes, quick totals

MINX

Minimum with row-by-row evaluation.

MINX([Monthly Report], [First_Class_Mail] * 0.5)

Power: Find minimum of calculated values
Use Cases: Adjusted thresholds, scaled minimums

MAXX

Maximum with row-by-row evaluation.

MAXX([Monthly Report], [Return_Mail] * 2)

Power: Find maximum of calculated values
Use Cases: Peak calculations, scaled maximums

💡 Pro Tips

Performance Optimization

Use SUM instead of SUMX for simple column sums
DISTINCTCOUNT is optimized for large datasets
COUNTROWS is fastest for total row counts

Common Patterns

Percentage of Total:

DIVIDE(
    SUM([Sales].Amount),
    CALCULATE(SUM([Sales].Amount), ALL([Products]))
)

Running Total:

SUMX(
    FILTER([Sales], [Sales].Date <= MAX([Sales].Date)),
    [Sales].Amount
)

Conditional Sum:

SUMX(
    FILTER([Sales], [Sales].Status = "Completed"),
    [Sales].Amount
)

📚 Practice Exercises

Calculate the average of top 10 values
Find the sum of values above average
Count distinct customers with purchases > $100

AXEL Aggregation Functions - SUM, AVG, COUNT & More