Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
334 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
You can achieve the same result by using this alternative syntax:
=INT(Products[ReorderLevel]/2)
RAND()
There are two functions for generating random numbers, RAND() and
RANDBETWEEN(). RAND() accepts no parameters and returns a random number
between 0 and 1. The examples can be used in any table or as part of a measure.
Syntax
=RAND()
=RAND()*(10-1)+1
Chapter 11: Math & Trig Functions 335
Result
Analysis
The result shown is from the second example. It demonstrates how to generate a
random number between 1 and 10. Your results will differ. Also, the result is different
for every row. If you refresh the table, or force a manual recalculation, the results will
change.
RANDBETWEEN()
RANDBETWEEN() takes two parameters, a bottom boundary number and a top
boundary number. It returns random integers between those two numbers, inclusive.
The example can be used in any table or as part of a measure.
Syntax
=RANDBETWEEN(1,10)
336 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
If you recalculate your DAX functions, or refresh the source data, which forces
recalculation, then both RAND() and RANDBETWEEN() as calculated columns will
return different results. In the example, RANDBETWEEN returns whole numbers. Your
results will differ. This is different from the second example in the previous query:
=RAND()*(10-1)+1
ROUND()
Coming up are three more rounding functions for you to try. ROUND() is the first
one. ROUND() will round up or round down. It differs from MROUND() in that the
second parameter is the number of significant figures rather than a unit or multiple of
significance. The example uses the Products table.
Syntax
=ROUND(Products[UnitPrice],-1)
Chapter 11: Math & Trig Functions 337
Result
Analysis
ROUND() can round down (like ROUNDDOWN()) or round up (like ROUNDUP()). The
second parameter in this example is negative: -1 means “round to an integer value.”
If you wish to round to a number of decimal places, then make the second parameter
positive. Negative values for the second parameter round to the left of the decimal
point. It may be useful to compare the products Chang and Chef Anton’s Cajun
Seasoning. The first is rounded up, and the second is rounded down.
ROUNDDOWN()
ROUNDDOWN(), unlike ROUND(), always rounds down. If the second argument is
negative, it rounds down to the left of the decimal point. The example uses the Products
table.
Syntax
=ROUNDDOWN(Products[UnitPrice],-1)
338 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
It might be instructive to look at Chang and Chef Anton’s Cajun Seasoning again.
ROUNDUP()
As you probably expect, ROUNDUP() always rounds up. A negative second parameter
rounds up to the left of the decimal point. The example uses the Products table.
Syntax
=ROUNDUP(Products[UnitPrice],-1)
Chapter 11: Math & Trig Functions 339
Result
Analysis
Once more, please take a look at both Chang and Chef Anton’s Cajun Seasoning.
Hopefully, you can see the difference between ROUND(), ROUNDDOWN(), and
ROUNDUP(). If you would like to experiment further, you may like to try the following
example, which has a positive second parameter, and look at Chef Anton’s Gumbo Mix:
=ROUNDUP(Products[UnitPrice],1)
SIGN()
You might use SIGN() to help you identify positive and negative numbers in a pivot
table. The function returns a zero for zero values. The example uses the Products table.
Syntax
=SIGN(Products[UnitsInStock]-Products[ReorderLevel])
340 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
If you scroll down to Nord-Ost Matjeshering, it looks as if we forgot to place an order
with our supplier!
SQRT()
SQRT() simply returns the square root of a number or a numeric column or numeric
expression. If the expression used as the only parameter evaluates to a negative number,
SQRT() returns an error. The example uses the Products table.
Syntax
=SQRT(Products[ReorderLevel])
Chapter 11: Math & Trig Functions 341
Result
Analysis
Maybe we ought to use one of the rounding functions with the result for Chai.
SUM()
Although we are using SUM() in a calculated column, it’s a very common function to
use in a measure as well. SUM() is officially a Math & Trig function; that’s why it’s
included here. However, it has a lot in common with the Statistical functions, which
is why it was also covered in the earlier chapter on aggregate (that is, Statistical with
SUM() and SUMX()) functions. The example uses the Products table.
Syntax
=SUM(Products[UnitsInStock])
342 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
The formula returns the total number of all products in stock. This may or may not
be a valid aggregation; that’s a business decision. The result could be divided into
UnitsInStock for each product to calculate the percentage contribution to total stock.
SUMX()
Like SUM(), SUMX() is also a Math & Trig function. It too was covered earlier when
we looked at aggregate functions. SUMX() is one of the X-functions, which means it
requires a table (and not a numeric value) as its first parameter. The example uses the
Categories table.
Chapter 11: Math & Trig Functions 343
Syntax
=SUMX(RELATEDTABLE(Products),Products[UnitsInStock])
Result
Analysis
Here RELATEDTABLE() is being used to return a table as the first parameter for
SUMX(). The second parameter is a numeric column in the referenced table. The result
indicates that, for example, we have 559 beverage products in stock. If you add the
figures for each row, the answer is 3119, the result of our total stock from the previous
query.
TRUNC()
This is our final Math & Trig function. TRUNC() strips away decimals to leave a whole
number. The example uses the Products table.
Syntax
=TRUNC(Products[UnitPrice])