Microsoft Office Tutorials and References
In Depth Information
column is always considered 0. Empty cells are never considered in the calculation. So
even if these functions can be used on non-numeric columns without retuning an error,
their results are not always the same as Excel because there is no automatic conversion to
numbers for text columns. These functions are named AVERAGEA, COUNTA, MINA, and
MAXA , and you can see in Figure 3-17 an example of their usage in measures operating
on a TRUE/FALSE column of the sample table shown in the same worksheet: the table is
used as a linked table in PowerPivot, and the lower part of the picture is a PivotTable based
on that PowerPivot data.
FIguRE 3-17 TRUE/FALSE are evaluated as 1/0 in A-suffixed statistical functions.
Despite the same name of statistical functions, the difference in the way they are used in
DAX and Excel exists because in PowerPivot a column has a type and its type determines the
behavior of aggregation functions. Excel handles a type for each cell, whereas PowerPivot
handles a type for each column. PowerPivot deals with data in tabular form (technically it is
called relational data) with well-defined types for each column, whereas Excel formulas work
on heterogeneous cell values, without well-defined types. If a column in PowerPivot is of a
number type, all the values can be only numbers or empty cells. If a column is of a text type,
it is always 0 for these functions, even if the text can be converted to a number, whereas in
Excel the value is considered a number on a cell-by-cell basis. For these reasons, these DAX
functions are not very useful for Text type columns.
Figure 3-18 shows a comparison of the calculation made by the same functions in Excel and
PowerPivot, using the same data you can see in the table on the left of the worksheet. The
text value N/A in the Quantity column in Excel is considered 0 by calculations of AVERAGEA,
MINA, MAXA, and COUNTA made by Excel 2010, as you can see in the second row of the
worksheet. However, the calculation of the same measure in PowerPivot considers the value
to be 0 for any cell (see the fifth row) because the whole Quantity column is imported as
Text in the corresponding PowerPivot table.