ROUND
Rounds a number to a set number of decimal places, or to the left of the decimal point.
ROUND(<number>, <num_digits>)Returns: A number (a scalar).
What it does
ROUND rounds a number to the number of digits you give. With 2 digits, it rounds to cents. With -3 digits, it rounds to the nearest thousand. Halves are rounded away from zero, so 2.5 becomes 3 and -2.5 becomes -3. Use ROUND when a calculated amount must be stated to a precision that matches the source, such as tax to the cent.
Syntax and arguments
- number: the value to round.
- num_digits: the number of decimal places to keep. 0 rounds to a whole number. A negative value rounds to tens, hundreds or thousands. Use 2 for cents.
Example
A sales tax of 8.25 percent is applied to each invoice. The tax must be stated to the cent, as the invoice would show it.
Sales Tax = ROUND(Invoices[TaxableAmount] * 0.0825, 2)
Each row is rounded to two decimal places. Summing the rounded tax gives the figure that matches the printed invoices, while the unrounded total could differ by a few cents.
Example: a second, different use
A board pack shows sales in thousands. The total is rounded to the nearest thousand so the figure reads cleanly:
Total Sales (thousands) = ROUND([Total Sales], -3)
A negative digit count rounds to the left of the decimal point. The underlying number stays precise for other calculations, so use this only for a display measure.
Things to watch
- ROUND changes the value. Other measures built on a rounded value inherit the rounding, so round as late as possible.
- Formatting a number to show two decimals does not round it. Use ROUND when the stored value must match.
- Rounding each row and then summing can differ from summing and rounding once. Decide which the source document uses.
- Floating point arithmetic can leave a value such as 2.4999999. Rounding to the wanted digits resolves that before a comparison.
- Round half away from zero is not banker's rounding. Test a sample if an exact match with another system is needed.
In business terms
ROUND keeps calculated money in line with what a document shows: tax on an invoice, interest on a loan, a commission amount, a rounded allocation. Auditors check the rounding rule, so write it down and use it the same way across the model.