Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
234 Practical PowerPivot & DAX Formulas for Excel 2010
COUNTBLANK()
This is yet another counting function. COUNTBLANK(), as its name suggests, counts the
blank values in a column.
This example is a calculated column in the Customers table.
Syntax
=COUNTBLANK(Customers[Region])
Result
Analysis
There are 60 customers that have a blank entry in the Region column. Zeros are not
blanks, and are not counted.
COUNTROWS()
Here we have the last of the counting functions in this chapter.
These examples are calculated columns in the Employees table.
Syntax
=COUNTROWS(Orders)
=COUNTX(RELATEDTABLE(Orders),Orders[EmployeeID])
=(Employees[CalculatedColumn2] / Employees[CalculatedColumn1]) * 100
Chapter 7: Aggregate Functions 235
Result
Analysis
Davolio had nearly 15 percent of the orders. COUNTROWS() tells you the total number of
rows in the Orders table. COUNTX() tells you the total number of rows in the Orders table
in the row context of the current employee. The second example could be replaced with
=COUNTROWS(RELATEDTABLE(Orders))
However, COUNTX() is more flexible, as it lets you specify a specific column to count.
MAX()
MAX() returns the maximum value in a column.
This example is a calculated column in the Orders table.
Syntax
=MAX(Orders[Freight])
Result
236 Practical PowerPivot & DAX Formulas for Excel 2010
Analysis
MAX() can handle numbers or dates. For other data types, use MAXA(). The result
shows the largest freight charge for any order.
MAXX()
The X-function version of MAX(), MAXX(), returns the maximum numeric or date
value in a table column. The column itself can be the result of an expression, as shown
in this example. There is no MAXAX() function.
This example is a calculated column in the Orders table.
Syntax
=MAXX(RELATEDTABLE('Order Details'),
'Order Details'[Quantity] * 'Order Details'[UnitPrice])
Result
Analysis
This example uses an expression for the second, column, parameter of MAXX(). You
could also reference either a regular or a calculated column—here you might try the
Sales Amount calculated column. The result shows the maximum sales amount from
the order lines for each order.
MAXA()
MAXA() can cope with values that are neither numeric nor dates.
These examples are calculated columns in the Products table.
Chapter 7: Aggregate Functions 237
Syntax
=MAX(Products[Discontinued])
=MAXA(Products[Discontinued])
Result
Analysis
The first example is for MAX()—it causes an error. The second example uses MAXA()
and handles the TRUE/FALSE Discontinued column. A value of TRUE counts as 1,
while blanks, text, and FALSE count as zero.
MIN()
MIN() works on numeric or date columns.
This example is a calculated column in the Orders table.
Syntax
=MIN(Orders[Freight])
Result
238 Practical PowerPivot & DAX Formulas for Excel 2010
Analysis
The minimum freight charge for any order is only two cents (or two pence). The
currency unit is determined by the regional settings in Control Panel and by the
settings in the Formatting group of the PowerPivot Home ribbon.
MINX()
MINX() is the X-function table version of MIN().
This example is a calculated column in the Orders table.
Syntax
=MINX(RELATEDTABLE('Order Details'),'Order Details'[Quantity] * 'Order
Details'[UnitPrice])
Result
Analysis
The formula returns the minimum sales amount of all the order lines belonging to a
particular order.
MINA()
Here we have the A-function variation on the MIN() function.
These examples are calculated columns in the Products table.
Syntax
=MIN(Products[Discontinued])
=MINA(Products[Discontinued])
Chapter 7: Aggregate Functions 239
Result
Analysis
The first example results in an error as the Discontinued column values are neither
numeric nor dates. In the second example, TRUE is counted as 1 and FALSE as zero.
MINA(), therefore, returns zero.
SUM() 1/2
This function, SUM(), and its related function SUMX(), are officially Math & Trig
functions. You can verify this in the Insert Function dialog. However, they are also
introduced in this chapter as they do perform an aggregation.
This example is a calculated column in the Order Details table.
Syntax
=SUM('Order Details'[Sales Amount])
Result
240 Practical PowerPivot & DAX Formulas for Excel 2010
Analysis
The formula is working out the total value of all sales in the Order Details table. You
might divide into the Sales Amount calculated column to ascertain the percentage
contribution of each order line to overall sales.
SUM() 2/2
It probably bears reiterating that DAX functions and formulas can (in most cases) be
used in either calculated columns or measures. In the previous example, SUM() was a
calculated column.
This example is a measure.
Syntax
=SUM('Order Details'[Sales Amount])
Result
Analysis
The pivot table in the result depicts total sales by product category, and the grand total
for sales for all categories.
SUMX()
SUMX() is the X-function version of SUM(). It requires a table as its first parameter.
This example is a calculated column in the Categories table.
Chapter 7: Aggregate Functions 241
Syntax
=SUMX(RELATEDTABLE(Products),Products[UnitsInStock])
Result
Analysis
The first argument must be a table or another function that returns a table. The second
argument is the column you wish to aggregate. Here, the result shows the total number
of stock items for each product category.
This page intentionally left blank
Date & Time Functions
1/2: Basic Functions
Chapter 8