Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 56 ยท DAX

DAX Math and Statistical Functions

Learn DAX math and statistics: ROUND, MROUND, CEILING, MOD, QUOTIENT, POWER, LOG, MEDIAN, STDEV.S, PERCENTILE and COMBIN, checked with Python stand-ins.

Averages and totals are only the start. Pricing rules round to the nearest 5 cents, schedules need remainders, quality teams want the median and the standard deviation, and analysts quote the 90th percentile of a delivery time. DAX includes a full set of math and statistical functions for this. Most work exactly like their Excel namesakes, but a few have behavior that will bite you if you assume Python or SQL rules.

DAX cannot run on this site, so the formulas are read-only. The Python stand-ins compute the same results, and each one calls out where Python's built-in behavior is different from DAX. That difference is the main lesson here.

Rounding

DAX
Round 2dp     = ROUND ( [Price], 2 )
Round Up      = ROUNDUP ( [Price], 0 )
Round Down    = ROUNDDOWN ( [Price], 0 )
Nearest 5c    = MROUND ( [Price], 0.05 )
Next Multiple = CEILING ( [Qty], 12 )
Prev Multiple = FLOOR ( [Qty], 12 )
Whole Number  = INT ( [Price] )
Cut Off       = TRUNC ( [Price] )
Function What it does 2.5 -3.7
ROUND(x, 0) nearest, halves away from zero 3 -4
ROUNDUP(x, 0) away from zero 3 -4
ROUNDDOWN(x, 0) toward zero 2 -3
INT(x) down to the next lower integer 2 -4
TRUNC(x) drops the decimals 2 -3

The number of digits can be negative: ROUND(1234, -2) is 1,200. MROUND(x, multiple) rounds to the nearest multiple, CEILING always rounds up to a multiple (good for "boxes needed") and FLOOR rounds down. Both arguments of MROUND must have the same sign, otherwise it fails.

DAX ROUND(2.5, 0) is 3, and ROUND(3.5, 0) is 4: halves go away from zero, as in school. Python's built-in round(2.5) is 2, because Python uses "banker's rounding" (halves go to the even number). If you verify DAX in Python, use the decimal module with ROUND_HALF_UP, as below.

Try it: DAX rounding in Python

Python Stand-in: ROUND, INT, TRUNC and MROUND

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Division helpers: MOD, QUOTIENT, DIVIDE

DAX
Remainder = MOD ( [Items], 12 )
Full Boxes = QUOTIENT ( [Items], 12 )
Is Even = MOD ( [Number], 2 ) = 0
Safe Ratio = DIVIDE ( [A], [B], 0 )

MOD(n, d) returns the remainder and takes the sign of the divisor, so MOD(-7, 3) is 2. QUOTIENT(n, d) returns the integer part of the division and truncates toward zero, so QUOTIENT(-7, 3) is -2. Python's // rounds toward minus infinity and gives -3, so the two are not the same for negative numbers. GCD(a, b) and LCM(a, b) give the greatest common divisor and least common multiple.

Powers, roots and logs

DAX
Square     = POWER ( [Side], 2 )
Root       = SQRT ( [Area] )
Log Base 2 = LOG ( [Number], 2 )
Natural    = LN ( [Number] )
Decimal    = LOG10 ( [Number] )
E to x     = EXP ( [x] )
Compound   = [Principal] * POWER ( 1 + [Rate], [Years] )

POWER also powers the compound-growth formula that finance reports use. The compound annual growth rate between a first and last value over n years is POWER(Last / First, 1 / n) - 1, wrapped in DIVIDE to guard against a zero start. SQRT of a negative number is an error, so guard it with IF([x] >= 0, SQRT([x])).

Combinatorics and randomness

COMBIN(n, k) is the number of ways to choose k of n items (order does not matter), PERMUT(n, k) counts ordered arrangements and FACT(n) is the factorial. RAND() returns a random number in [0, 1) and RANDBETWEEN(low, high) a random whole number. Random functions are volatile: they produce a different value on every evaluation, so a measure using them changes whenever a visual refreshes. Use them to create test data in a calculated table, not in a report that others will rely on.

Statistical aggregations

DAX
Median Order  = MEDIAN ( Orders[Amount] )
Median by Day = MEDIANX ( VALUES ( 'Date'[Date] ), [Total Sales] )
Std Dev       = STDEV.S ( Orders[Amount] )
Std Dev Pop   = STDEV.P ( Orders[Amount] )
Variance      = VAR.S ( Orders[Amount] )
P90           = PERCENTILE.INC ( Orders[Amount], 0.9 )
P25 Excl      = PERCENTILE.EXC ( Orders[Amount], 0.25 )
Geo Mean      = GEOMEAN ( Orders[GrowthFactor] )
Std by Month  = STDEVX.S ( VALUES ( 'Date'[Month] ), [Total Sales] )

The functions that end in X are iterators: they take a table and an expression, evaluate the expression for each row and then aggregate. That is how you take the median or standard deviation of a measure (such as daily sales), not of a raw column. This builds on Aggregators and Iterators. Without X, the function reads a column directly.

A few points to get right:

  • .S versus .P. STDEV.S and VAR.S treat the data as a sample (divide by n - 1); STDEV.P and VAR.P treat it as the whole population (divide by n). For measuring a sample of orders to estimate a process, use .S.
  • PERCENTILE.INC interpolates between values and includes the endpoints (0 to 1). PERCENTILE.EXC excludes them and only works for k between 1/(n+1) and n/(n+1).
  • MEDIAN is robust to outliers. Report it beside the average when the data is skewed: one huge order can pull an average far from the typical order.
  • GEOMEAN is the right average for growth factors and ratios.

Try it: the statistics on a small sample

Python Stand-in: median, standard deviation and percentiles

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A single order of 1,000 lifts the average to 145 but the median stays at 55. The geometric mean of the growth factors is below the plain mean, which is the right answer when growth compounds.

Negative numbers: MOD, QUOTIENT and INT

Python Stand-in: negative numbers do not behave like Python

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

For -7 and 3, QUOTIENT gives -2 and Python's // gives -3. If your measure splits items into full boxes and leftovers, the sign of the data decides which function is right, so test negative values (returns, credit notes) explicitly.

Practical advice

  • Round at the end. Rounding each line and then adding them up gives a different total from rounding the total. Keep full precision in the calculation and round only the presented result (or set the number format instead of rounding).
  • Use DIVIDE for all ratios.
  • Prefer the iterator version (MEDIANX, STDEVX.S) when the value you analyze is a measure.
  • Do not use RAND in published reports.
  • Statistical functions ignore blank values, but include zeros, which can change the average and median.

DAX also has trigonometric, depreciation, bond-coupon and bitwise functions; they are covered in DAX Trigonometric, Financial and Bitwise Functions.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does DAX ROUND(2.5, 0) return?

    DAX rounds halves away from zero. Python's round(2.5) gives 2 because of banker's rounding, so do not use it to check DAX.
  2. 2. You sell items in boxes of 12 and need the number of boxes for 25 items. Which expression gives 3?

    CEILING rounds up to the next multiple, so CEILING(25 / 12, 1) is 3. QUOTIENT gives 2 full boxes and MOD gives the remainder 1.
  3. 3. Which function takes the standard deviation of a measure evaluated for each day?

    The X versions iterate a table and evaluate an expression per row, so they work with measures.
  4. 4. Why is MEDIAN often reported beside AVERAGE?

    One very large value moves the average a lot but barely moves the median.
  5. 5. Which pair describes sample versus population standard deviation?

    STDEV.S divides by n - 1 (sample); STDEV.P divides by n (population).

Summary

  • ROUND rounds halves away from zero; INT, TRUNC, CEILING, FLOOR and MROUND give you other rounding rules, and Python's round differs from DAX.
  • MOD and QUOTIENT split numbers into remainders and whole parts; negative numbers behave differently from Python's //.
  • POWER, SQRT, LOG, LN and EXP cover powers and growth.
  • MEDIAN, STDEV.S/.P, VAR.S/.P, PERCENTILE.INC/.EXC and GEOMEAN summarize distributions; the X versions work on measures.
  • Avoid volatile RAND in shared reports, and round only at the end.

Next: DAX Table Functions.