Supported functions

Every function available in an Axmo formula — logical, arithmetic, aggregation, financial and dimension navigation.

These are the functions available inside an Axmo formula. This is all of them: there is no hidden set, and you cannot add your own.

A function's name is not case-sensitive. SUM, sum and Sum are the same function, and the casing below is a convention rather than a requirement. There is one alternative spelling in the whole language — AVERAGE is another way of writing AVG — and no other function has a second name.

Functions that work on a single number work element by element when you give them a set: ABS(Profit[Month]) gives you a set of absolute values, one per month, not one number. Functions that reduce — SUM, AVG, MAX on its own — are the ones that turn a set into a single value.

Everything here operates on numbers. There are no text functions and no date functions, because a model holds figures and its structure is carried by the dimensions rather than by what is written in a cell.

Logical

IF(condition, then)                either branch, chosen per coordinate
IF(condition, then, otherwise)
AND(a, b)                          1 when both are true
OR(a, b)                           1 when either is true
NOT(a)                             turns one into the other

There is no true or false type: a condition is a number, and anything other than zero counts as true.

AND and OR take exactly two arguments. For three conditions, nest them — AND(a, AND(b, c)).

One thing IF does that is worth knowing before it surprises you: the branch not taken does not drag its dimensions into the series. A formula that reads a product-level figure only in one branch does not become a product-level series because of it. The series takes the shape of what it actually computed.

Missing values

Nz(expression, fallback)           the fallback, when the expression is null
ISEMPTY(expression)                1 when the expression is null, 0 when it is not
ISERROR(expr)                      whether a value is an error, in the shape it was given
IFERROR(expr, fallback)            expr where it has an answer, fallback where it failed

Nz is how nearly every null is handled in practice — it reads as if this is nothing, use that instead. ISEMPTY is for the cases where you want to branch on it rather than substitute for it.

ISEMPTY always gives a single number, whatever you hand it.

ISERROR returns the shape it was given. Over a set it answers per element, so SUM(ISERROR(…)) counts the failures in that set.

IFERROR is the armouring idiom for a failure, as Nz is for an empty cell. Over a set it substitutes element by element, so only the slots that failed take the fallback.

Arithmetic

ABS(x)                             drop the sign
SQRT(x)                            square root
EXP(x)                             e raised to x
LN(x)                              natural logarithm
POWER(base, exponent)              the same as base ^ exponent
MOD(a, b)                          remainder after dividing
CEIL(x)                            up to the next whole number
FLOOR(x)                           down to the last whole number
ROUND(x)                           to the nearest whole number
ROUND(x, places)                   to that many decimal places

All of these work element by element, so a set in gives a set out.

ROUND rounds halves away from zero, matching Excel: ROUND(2.5) is 3 and ROUND(-2.5) is -3. A negative number of places rounds to powers of ten, so ROUND(1234, -2) is 1200 — useful for presenting figures in thousands without dividing.

Aggregation

SUM(values)                        add them up
AVG(values)                        the mean — AVERAGE is the same function
AVGA(values)                       the mean, counting an empty entry as zero
MEDIAN(values)                     the middle value
PERCENTILE(values, p)              p between 0 and 1
MAX(values)                        the largest
MAXA(values)                       the maximum, counting an empty entry as zero
MIN(values)                        the smallest
MINA(values)                       the minimum, counting an empty entry as zero
COUNT(values)                      how many are not null
COUNT(dimension-expression)        how many elements the expression selects
RANK(value, values)                its position, largest first
RANK(value, values, ASC)           its position, smallest first

These are how you collapse a set into one number, and there is no separate family of dimensional functions to learn: you select what you want with a filter and wrap it in the one that reduces it.

MAX and MIN change meaning with the number of arguments. Given one, they reduce a set to its largest or smallest value. Given two, they compare those two element by element and keep their shape — MAX(Profit, 0) is how you floor a series at zero, and it stays a series.

COUNT also has two readings, and it tells them apart by what you give it. Hand it values and it counts the ones that are not null. Hand it a dimensional expression — COUNT(Product), COUNT(Europe/_) — and it counts elements, whether or not anything has been calculated for them.

Nulls are skipped rather than treated as zero, so a MEDIAN over a partly-empty set is the median of what is there. Where everything is null, the result is null.

PERCENTILE interpolates between the two nearest values, and PERCENTILE(x, 0.5) is the same as MEDIAN(x).

Some aggregations come in pairs, and the A means ALL.

AVG, MIN and MAX skip children with no value. AVGA, MINA and MAXA include them, counting each as zero. Which you want depends on the question: the average sale price across the shops that traded, or the average across every shop including those that sold nothing.

SUM needs no pair — an empty child adds nothing either way.

FIRST and LAST have no pair, and nothing is missing. They do not calculate over children; they select one by its position and return whatever it holds. The A distinction is about which children contribute to a calculation, so there is nothing here for it to distinguish.

Each of the six paired names is both a function you can write and a roll-up method you can choose.

AVGA, MINA and MAXA refuse a single value. AVGA(5) is an error. That is the rule for every aggregate — SUM(5) refuses identically — not a peculiarity of the -A family. In practice the argument is a filtered series expression such as AVGA(Revenue[Geography/_]).

MINA and MAXA have no two-argument form. MIN and MAX accept two single values and return the smaller or larger; MINA(1, 2) is refused. They aggregate a set and nothing else.

Financial

PV(rate, nper, pmt, [fv], [type])              present value
FV(rate, nper, pmt, [pv], [type])              future value
PMT(rate, nper, pv, [fv], [type])              the level payment
RATE(nper, pmt, pv, [fv], [type], [guess])     the implied rate
NPV(rate, cashflows)                           net present value
IRR(cashflows)                                 internal rate of return
IRR(cashflows, guess)
SLN(cost, salvage, life)                       straight-line depreciation
SYD(cost, salvage, life, period)               sum-of-years'-digits
DDB(cost, salvage, life, period)               declining balance
DDB(cost, salvage, life, period, factor)

Reading the signatures. In a signature, square brackets mark a parameter you may leave out — so PV(rate, nper, pmt, [fv], [type]) takes three arguments or five. Inside a formula the same brackets mean something else: Revenue[Europe, 2025] is a filter, and there the brackets are part of the expression rather than a note about it.

These follow Excel's definitions, including its sign conventions: a payment out is negative, a payment in is positive. type is 0 for payments at the end of a period and 1 for the beginning.

NPV and IRR take a set of cashflows — ordinarily a series filtered across a range of periods. They read that set in the order the dimension gives them, and they do not check that it has no gaps. If a filter can select a discontinuous set of periods, it is worth being deliberate about it, because a missing period shifts everything after it rather than raising anything.

RATE and IRR solve rather than calculate, so an unusual cashflow pattern may not converge. Where it does not, a guess near the answer you expect usually fixes it.

Where you are in a dimension

DimLevel(dimension-expression)                 how deep, counting the top as 1
DimPosition(dimension-expression)              which one it is within its layer
DimPosition(expression, ancestor)              which one it is beneath that ancestor
ISBEFORE(dimension)                            1 at the opening boundary element
ISAFTER(dimension)                             1 at the closing boundary element

DimPosition is the long form of the @ notation on the Dimensional expressions page. Month@Year written out is DimPosition(Month[0], Year[0]) — @ is the shorter spelling, and [0] is what it hides: the current one at that layer. Reach for the long form when the ancestor you want to count within is itself an expression, and whenever the longer spelling simply reads more clearly.

Two things about it are easy to get wrong. The [0] is not optional: DimPosition(Month, Year) names the whole layer rather than the current element, returns nothing, and still passes validation. And the second name must be a layer above the first, within the same dimension — the counterpart of Year in Month@Year.

Given an ancestor that is not actually above the element, DimPosition gives null rather than a number.

ISBEFORE and ISAFTER take a bare dimension name and ask whether the coordinate being calculated is standing on one of its boundary elements. Both are 0 at every real period, and both are 0 in a dimension that declares no boundary at all — so a formula written with them is safe in a model that has none.

What is not here, and why

No lookup functions. Nothing corresponds to VLOOKUP or INDEX/MATCH, because there is nothing to look up: a series is referred to by name, and a coordinate by naming the elements you mean. The problem those functions solve does not arise.

No functions for summing across a dimension. Filtering is what selects, and SUM is what adds. There is no SUMIF, and no dimensional variant of anything, because the filter already does that half.

No user-defined functions. The way to reuse a calculation is to make it a series and refer to it by name, which also makes it visible, inspectable and available to everything else in the model.