Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
324 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
This time, Chef Anton’s Gumbo Mix has changed from 21.35 to 21.3.
INT()
INT() rounds a number down to an integer. The example uses the Products table.
Syntax
=INT(Products[UnitPrice])
Chapter 11: Math & Trig Functions 325
Result
Analysis
If you scroll down to the product Schoggi Schokolade, the price of 43.9 has been
rounded down to 43. The same result could have been achieved by using the TRUNC()
function. INT() and TRUNC() differ in how they operate on negative numbers. The
following syntax, using FLOOR(), also produces the same result:
=FLOOR(Products[UnitPrice],1)
ISO.CEILING()
This function, ISO.CEILING(), is similar to CEILING(). The difference between
the two functions lies in how they behave when both parameters are negative. In that
situation, ISO.CEILING() will round up, while CEILING() will round down. The
second parameter is the unit of significance. The example uses the Products table.
Syntax
=ISO.CEILING(Products[UnitPrice],.1)
326 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
Chef Anton’s Gumbo Mix is now 21.4 rather than 21.35.
LN()
LN() returns the natural logarithm of a number. The example can be used in any table
or as part of a measure.
Syntax
=LN(5)
Chapter 11: Math & Trig Functions 327
Result
Analysis
There are two other logarithmic functions, LOG() and LOG10(). LN(), the natural
logarithm, has a base of the constant e. It’s the inverse of EXP().
LOG()
Here is the second of three logarithmic functions. LOG() accepts two parameters, a
number and the base for the logarithm. The example can be used in any table or as part
of a measure.
Syntax
=LOG(5,5)
328 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
If you omit the second parameter, it is assumed you want the logarithm to the base of
10—it then returns an identical result to the function LOG10().
LOG10()
LOG10() is the third of our three logarithmic functions. The example can be used in
any table or as part of a measure.
Syntax
=LOG10(100)
Chapter 11: Math & Trig Functions 329
Result
Analysis
LOG(100) would also return the same result as LOG10(100).
MOD()
MOD() is the modulo function. The second number is the divisor, the number you wish
to divide by. The example uses the Products table.
Syntax
=MOD(Products[ReorderLevel],2)
330 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
MOD() returns the remainder after the division. Here, the divisor is 2, and MOD() has
identified odd and even values. The product Chang, for example, has an odd number
(25) for its ReorderLevel.
MROUND()
There are quite a few rounding functions in DAX. MROUND() rounds up or down to
the nearest unit (or multiple, hence the name MROUND()) of significance. The example
uses the Products table.
Syntax
=MROUND(Products[UnitPrice],5)
Chapter 11: Math & Trig Functions 331
Result
Analysis
The price of the product, Mishi Kobe Niku, has been changed from 97 to 95. With
CEILING() the result would be 100. FLOOR() would have given the same result—but
FLOOR() and MROUND() would have returned different results for Tofu. MROUND()
can act as CEILING() or FLOOR(), depending on the number that is the first
parameter. There are even more rounding functions. INT() and TRUNC() always
round to an integer; their behavior is only different on negative numbers. ROUND(),
ROUNDDOWN(), and ROUNDUP() round to a given number of figures, rather than to a
multiple of significance like MROUND().
PI()
PI() returns the constant pi. It is accurate to 14 decimal places. The example can be
used in any table or as part of a measure.
Syntax
=PI()
332 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
This might be useful if you have a column that records a radius and you also use it in
conjunction with the POWER() function!
POWER()
The first parameter of POWER() is a number or a numeric column or an expression
evaluating to a number. The second parameter is the power to which to raise the
number. The example can be used in any table or as part of a measure.
Syntax
=POWER(4,3)
Chapter 11: Math & Trig Functions 333
Result
Analysis
If you did have a radius of a circle column, then that column could be the first
parameter. The second parameter could be 2 and you might then multiply by PI(), to
give you the area of a circle. Of course, POWER() and PI() have rather more uses than
that!
QUOTIENT()
QUOTIENT() divides the first numeric parameter by the second one and returns the
integer part of the result. The example uses the Products table.
Syntax
=QUOTIENT(Products[ReorderLevel],2)