AXEL Advanced Analytics

🚀 Advanced Analytics Functions

8 sophisticated functions for complex analysis scenarios.

RANKX

Rank values within a table.

RANKX(
    [Monthly Report],
    [First_Class_Mail],
    ,
    DESC
)

Returns: Rank number (1 = highest)
Use Cases: Leaderboards, percentiles

TOPN

Get top N rows based on criteria.

TOPN(
    3,
    [Monthly Report],
    [First_Class_Mail],
    DESC
)

Returns: Table with top 3 rows
Use Cases: Best performers, top products

LET

Define variables for complex expressions.

LET(
    Total, SUM([Monthly Report].First_Class_Mail),
    Avg, AVG([Monthly Report].First_Class_Mail),
    DIVIDE(Total, Avg)
)

Power: Improves readability and performance
Use Cases: Complex calculations, code reuse

IFERROR

Handle errors gracefully.

IFERROR(DIVIDE(1, 0), -1)

Returns: -1 instead of error
Use Cases: Robust calculations, default values

Get value from related table (many-to-one).

SUMX(
    [Sales],
    RELATED([Products].Price)
)

Requirement: Relationship must exist
Use Cases: Lookups, joins

RELATEDTABLE

Get related rows from child table (one-to-many).

SUMX(
    [Products],
    COUNTX(RELATEDTABLE([Sales]), 1)
)

Returns: Related rows for each parent
Use Cases: Count children, aggregate related

ISBLANK

Check if value is blank or empty.

ISBLANK([Value])

Returns: TRUE/FALSE
Use Cases: Data validation, conditional logic

💡 Advanced Patterns

Dynamic Ranking with Ties

RANKX(
    ALL([Products]),
    [Revenue],
    ,
    DESC,
    DENSE
)

Nested LET for Complex Calc

LET(
    BaseAmount, SUM([Sales].Amount),
    TaxRate, 0.08,
    Tax, BaseAmount * TaxRate,
    Total, BaseAmount + Tax,
    ROUND(Total, 2)
)

Safe Lookup Pattern

IFERROR(
    RELATED([Dimension].Value),
    "Not Found"
)

Top N with Others

UNION(
    TOPN(5, [Products], [Revenue]),
    ROW("Product", "Others", "Revenue", [TotalRevenue] - [Top5Revenue])
)

📊 Real-World Examples

Percentile Calculation

PERCENTILEX.INC(
    [Table],
    [Value],
    0.9
)

Moving Rank

RANKX(
    FILTER(
        [Sales],
        [Date] >= MAX([Date]) - 30
    ),
    [Amount]
)

Pareto Analysis (80/20)

FILTER(
    ADDCOLUMNS(
        [Products],
        "Rank", RANKX(ALL([Products]), [Revenue])
    ),
    [Rank] <= COUNTROWS([Products]) * 0.2
)

🎯 Best Practices

Use LET for complex multi-step calculations
Wrap lookups in IFERROR for safety
Consider performance with RANKX on large tables
Test relationships before using RELATED functions

Congratulations! 🎉

You've completed the AXEL function reference. Ready to put your knowledge to practice?

Next Steps:

Build your first AXEL dashboard
Join the Mars University Data Analytics community
Share your AXEL success stories

← Back to Overview

AXEL Advanced Analytics - RANKX, TOPN, LET & More