{"id":1699,"date":"2013-09-23T00:00:00","date_gmt":"2013-09-23T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/dax-statistical-functions\/"},"modified":"2021-08-24T13:39:50","modified_gmt":"2021-08-24T13:39:50","slug":"dax-statistical-functions","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/dax-statistical-functions\/","title":{"rendered":"DAX Statistical Functions"},"content":{"rendered":"
\n

\tThe Data Analysis Expressions (DAX) language includes a wide range of functions that help you refine your queries when retrieving data from a SQL Server Analysis Services (SSAS) tabular database. You’ve seen numerous examples of DAX functions throughout this series on the tabular model. One set of functions that are particularly useful when working with tabular data are the statistical functions, which support various ways to aggregate and analyze data. Statistical functions include not only the typical standbys, such as Min<\/strong><\/code> and Max<\/strong><\/code>, but also those that help structure that data into tables, such as AddColumns<\/strong><\/code> and Summarize<\/strong><\/code>. <\/p>\n

\tIn this article, the fifth in our series, we explore many of the DAX statistical functions and provide numerous examples that demonstrate how they work. As with the previous articles (one<\/a> | two<\/a> | three<\/a> | four<\/a>), the examples retrieve data from the AdventureWorks Tabular Model SQL 2012<\/strong><\/code> tabular database, available as a SQL Server Data Tools (SSDT) tabular project from the AdventureWorks CodePlex site<\/a>. I implemented the database on a local instance of SSAS 2012 in tabular mode and developed the examples against that database. <\/p>\n

\tTo follow along in this article, you should have a basic understanding of how to write a DAX query. The first two articles provide a good starting point if you’re new to DAX and SSAS tabular databases. Once you’ve read through those, you should have no problem applying the statistical functions we cover here. <\/p>\n

\tGiven the analytical nature of tabular databases, it’s not surprising that the statistical functions play such a key role; learning them is well worth the effort. <\/p>\n

The Row Function<\/h2>\n

\tOften when retrieving and aggregating tabular data, you’ll want to return only a single row in order to get a quick overview of your data. That’s where the Row<\/strong><\/code> function comes in. It returns a single-row table made up of one or more columns. You can use the function in conjunction with other functions in order to view different types of information. For example, the following query uses the Sum<\/strong><\/code> and Format<\/strong><\/code> functions in conjunction with the Row<\/strong><\/code> function in order to return the total amount of sales from the Sales Amount<\/strong><\/code> column in the Internet Sales<\/strong><\/code> table: <\/p>\n

evaluate\n(\n   row\n   (\n         \"Total Sales\", format(sum('Internet Sales'[Sales Amount]), \"Currency\")\n   )\n)\n<\/pre>\n
 \tAs you’ve seen in the previous articles, you must use the Evaluate<\/strong><\/code> statement to retrieve data from an SSAS tabular database. The statement takes one argument: a table or table expression. In this example, we use the Row<\/strong><\/code> function to create the table expression. <\/p>\n
 \tFor each column you want the Row<\/strong><\/code> function to return, you must specify the column name in double quotes followed by an expression that defines the value to be returned, with a comma separating the two components. In the example above, the Row<\/strong><\/code> function returns only one column named Total Sales<\/strong><\/code>, and the column expression uses the Sum<\/strong><\/code> function to return the sales total from Sales Amount<\/strong><\/code>. The expression then uses the Format<\/strong><\/code> function to return the value in a standard currency format (in US dollars). The following table shows the results returned by the Evaluate<\/strong><\/code> statement: <\/p>\n
\n\n\n\n\n
\n
Total Sales<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
$29,358,677.22<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tIf you want to return more than one column, when using the Row<\/strong><\/code> function, you must include a name\/expression pair for each one, separated by a comma, as shown in the following example: <\/p>\n
evaluate\n(\n   row\n   (\n      \"Total Sales\", format(sum('Internet Sales'[Sales Amount]), \"Currency\"),\n      \"Average Sales\", format(average('Internet Sales'[Sales Amount]), \"Currency\"),\n      \"Highest Sale\", format(max('Internet Sales'[Sales Amount]), \"Currency\"),\n      \"Lowest Sale\", format(min('Internet Sales'[Sales Amount]), \"Currency\")\n   )\n)<\/pre>\n
 \tEach column definition follows the same structure: column name followed by column expression. In this case, the column definitions differ only in the column name and the statistical function being applied. The Average<\/strong><\/code> function returns the average sales amount, the Max<\/strong><\/code> function return the highest sales amount, and the Min<\/strong><\/code> function returns the lowest. As the following results show, the Evaluate<\/strong><\/code> statement now returns four columns, but still one row: <\/p>\n
\n\n\n\n\n
Total Sales<\/strong><\/td>\nAverage Sales<\/strong><\/td>\nHighest Sale<\/strong><\/td>\nLowest Sale<\/strong><\/td>\n<\/tr>\n
$29,358,677.22<\/td>\n$486.09<\/td>\n$3,578.27<\/td>\n$2.29<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tWhen using a statistical function such as Average<\/strong><\/code>, it can also be useful to know how many rows are being evaluated. The following example includes both the Count<\/strong><\/code> and DistinctCount<\/strong><\/code> statistical functions: <\/p>\n
evaluate\n(\n   row\n   (\n      \"Total Sales\", format(sum('Internet Sales'[Sales Amount]), \"Currency\"),\n      \"Average Sales\", format(average('Internet Sales'[Sales Amount]), \"Currency\"),\n      \"Count\", count('Internet Sales'[Sales Amount]),\n      \"Distinct Count\", distinctcount('Internet Sales'[Sales Amount])\n   )\n)<\/pre>\n
 \tThe Count<\/strong><\/code> function returns the total number of values in the specified column, and the DistinctCount<\/strong><\/code> function returns the number of unique values in that column. The Count<\/strong><\/code> function does not work for string values, only number and date columns. Because we’re using the function on the Sales Amount<\/strong><\/code> column, it works fine because the column contains numerical data only. The following table shows the results now returned by the Evaluate<\/strong><\/code> statement: <\/p>\n
\n\n\n\n\n
Total Sales<\/strong><\/td>\nAverage Sales<\/strong><\/td>\nCount<\/strong><\/td>\nDistinct Count<\/strong><\/td>\n<\/tr>\n
$29,358,677.22<\/td>\n$486.09<\/td>\n60398<\/td>\n42<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tWhen working with the Count<\/strong><\/code> function, be aware that it counts only existing values. If a row is missing a value, that row is not counted. To get a count of all the rows in a table, you must use the CountRows<\/strong><\/code> statistical function, as shown in the following example: <\/p>\n
evaluate\n(\n   row\n   (\n      \"Average Cost\", format(average('Product'[Standard Cost]), \"Currency\"),\n      \"Count\", count('Product'[Standard Cost]),\n      \"Distinct Count\", distinctcount('Product'[Standard Cost]),\n      \"Row Count\", countrows('Product')\n   )\n)<\/pre>\n
 \tIn this case, the Standard<\/strong><\/code>Cost<\/strong><\/code> column in the Product<\/strong><\/code> table contains a number of empty cells, so a count based on that column returns a number less than the number of rows in the table, but the CountRows<\/strong><\/code> functions includes all rows, as the following results show: <\/p>\n
\n\n\n\n\n
Average Cost<\/strong><\/td>\nCount<\/strong><\/td>\nDistinct Count<\/strong><\/td>\nRow Count<\/strong><\/td>\n<\/tr>\n
$434.27<\/td>\n395<\/td>\n135<\/td>\n606<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tAs you’ve seen, the Row<\/strong><\/code> function can be useful whenever you need to return only a single row, which is often the case when aggregating data. However, the other statistical functions we’ve looked at are by no means limited to the Row<\/strong><\/code> function. For example, you can use them with the Summarize<\/strong><\/code> function when grouping data. Nevertheless, more often than not you’ll find that if you use the Row<\/strong><\/code> function, you’ll be using the aggregate-type statistical functions as well. <\/p>\n
The Topn Function<\/h2>\n
 \tThe Topn<\/strong><\/code> function limits the rows returned by a query to the top number of rows specified when you call the function, similar to how a TOP<\/strong><\/code> expression works in T-SQL. When you use the Topn<\/strong><\/code> function, you must include three parameters. The first specifies the number of rows to return. The second is the table or table expression from which you’re extracting the top rows. And the third is an expression that specifies the order in which the rows in the table should be sorted before returning the top rows. Usually this is one or more columns in the target table. <\/p>\n
 \tFor example, suppose you want to return the first five rows of the Sales Territory<\/strong><\/code> table, with the data sorted by the SalesTerritoryKey<\/strong><\/code> column. The following example shows how to call the Topn<\/strong><\/code> function and pass in the parameter values: <\/p>\n
evaluate\n(\n   topn(5, 'Sales Territory', [SalesTerritoryKey])\n)\n<\/pre>\n
 \tOnce again, we start with an Evaluate<\/strong><\/code> statement and specify the Top<\/strong><\/code>n<\/strong><\/code> function as the statement’s table expression. Into the function, we pass in the three required parameter values, which specify that the top five rows should be returned from the Sales Territory<\/strong><\/code> table, sorted by the SalesTerritoryKey<\/strong><\/code> column. The Evaluate<\/strong><\/code> statement returns the following data: <\/p>\n
\n\n\n\n\n\n\n\n\n
\n
SalesTerritoryKey<\/strong><\/p>\n<\/td>\n
\n
Sales    Territory Region<\/strong><\/p>\n<\/td>\n
\n
Sales    Territory Country<\/strong><\/p>\n<\/td>\n
\n
Sales    Territory Group<\/strong><\/p>\n<\/td>\n
\n
Sales    Territory Image<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
7<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
Europe<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n
\n
9<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
Pacific<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n
\n
8<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
Europe<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n
\n
10<\/p>\n<\/td>\n
\n
United    Kingdom<\/p>\n<\/td>\n
\n
United    Kingdom<\/p>\n<\/td>\n
\n
Europe<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n
\n
11<\/p>\n<\/td>\n
\n
NA<\/p>\n<\/td>\n
\n
NA<\/p>\n<\/td>\n
\n
NA<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tOne thing that might seem odd about the results is that they return rows with the SalesTerritoryKey<\/strong><\/code> values of 7<\/strong><\/code> through 11<\/strong><\/code>. If you’re familiar with the data, you know that the first value is 1<\/strong><\/code>. We’ll get into more details about this in a bit, but for now, know that the Topn<\/strong><\/code> function, by default, sorts the data in descending order, unlike most sort operations you’ll come across. <\/p>\n
 \tAnother odd aspect of these results is that they’re not ordered according to the SalesTerritoryKey<\/strong><\/code> column, as you might expect. Although you have to specify the order when calling the Topn<\/strong><\/code> function, that order applies only when determining which rows to return, not when ordering the result set. For that, you need to add an Order<\/strong><\/code>By<\/strong><\/code> clause that specifies the SalesTerritoryKey<\/strong><\/code> column, as shown in the following example: <\/p>\n
evaluate\n(\n   topn(5, 'Sales Territory', [SalesTerritoryKey])\n)\norder by 'Sales Territory'[SalesTerritoryKey]\n<\/pre>\n
 \tNow our statement will return the data in the specified order, as the following results show: <\/p>\n
\n\n\n\n\n\n\n\n\n
\n
SalesTerritoryKey<\/strong><\/p>\n<\/td>\n
\n
Sales    Territory Region<\/strong><\/p>\n<\/td>\n
\n
Sales    Territory Country<\/strong><\/p>\n<\/td>\n
\n
Sales    Territory Group<\/strong><\/p>\n<\/td>\n
\n
Sales    Territory Image<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
7<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
Europe<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n
\n
8<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
Europe<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n
\n
9<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
Pacific<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n
\n
10<\/p>\n<\/td>\n
\n
United    Kingdom<\/p>\n<\/td>\n
\n
United    Kingdom<\/p>\n<\/td>\n
\n
Europe<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n
\n
11<\/p>\n<\/td>\n
\n
NA<\/p>\n<\/td>\n
\n
NA<\/p>\n<\/td>\n
\n
NA<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tThe Topn<\/strong><\/code> function actually supports a fourth parameter, which determines whether the rows are sorted in ascending or descending order. As mentioned above, by default, they’re sorted in descending order. However, you can specify ascending or descending my adding a 0<\/strong><\/code> for descending or a 1<\/strong><\/code> for ascending. For example, the following Evaluate<\/strong><\/code> statement returns the top five rows in ascending order: <\/p>\n
evaluate\n(\n   topn(5, 'Sales Territory', [SalesTerritoryKey], 1)\n)\nprder by 'Sales Territory'[SalesTerritoryKey]\n<\/pre>\n
   The only difference between this example and the previous one is the addition of the fourth parameter for the Topn<\/strong><\/code> function. Now the statement returns the first five rows in the table, as shown in the following results:<\/p>\n
\n\n\n\n\n\n\n\n\n
\n
SalesTerritoryKey<\/strong><\/p>\n<\/td>\n
\n
Sales    Territory Region<\/strong><\/p>\n<\/td>\n
\n
Sales    Territory Country<\/strong><\/p>\n<\/td>\n
\n
Sales    Territory Group<\/strong><\/p>\n<\/td>\n
\n
Sales    Territory Image<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
1<\/p>\n<\/td>\n
\n
Northwest<\/p>\n<\/td>\n
\n
United    States<\/p>\n<\/td>\n
\n
North    America<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n
\n
2<\/p>\n<\/td>\n
\n
Northeast<\/p>\n<\/td>\n
\n
United    States<\/p>\n<\/td>\n
\n
North    America<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n
\n
3<\/p>\n<\/td>\n
\n
Central<\/p>\n<\/td>\n
\n
United    States<\/p>\n<\/td>\n
\n
North    America<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n
\n
4<\/p>\n<\/td>\n
\n
Southwest<\/p>\n<\/td>\n
\n
United    States<\/p>\n<\/td>\n
\n
North    America<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n
\n
5<\/p>\n<\/td>\n
\n
Southeast<\/p>\n<\/td>\n
\n
United    States<\/p>\n<\/td>\n
\n
North    America<\/p>\n<\/td>\n
\n
System.Byte[]<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tYou can also use the Topn<\/strong><\/code> function in conjunction with other functions. For example, the following Evaluate<\/strong><\/code> statement returns the top five rows from the table returned by the Summarize<\/strong><\/code> function: <\/p>\n
evaluate\n(\n   topn(5,\n      summarize\n      (\n         'Internet Sales',\n         'Product'[Product Name],\n         'Date'[Calendar Year],\n         \"Sales Amount\", format(sum('Internet Sales'[Sales Amount]), \"currency\")\n      ),\n   [Sales Amount], 0)\n)\norder by [Sales Amount] desc<\/pre>\n
 \tIn the previous examples, I specified the Sales<\/strong><\/code>Territory<\/strong><\/code> table as the second parameter of the Topn<\/strong><\/code> function, but this time around I’m using the Summarize<\/strong><\/code> function as the table expression. The function returns the Product Name<\/strong><\/code> and Calendar Year<\/strong><\/code> columns, along with a calculated column that provides a sales total for each group of data. I then use the Topn<\/strong><\/code> function to return only the first five rows of the data returned by the Summarize<\/strong><\/code> function. Notice that I specify the calculated column (Sales<\/strong><\/code>Amount<\/strong><\/code>) as the sort column, in descending order. The following table shows the results returned by the Evaluate<\/strong><\/code> statement: <\/p>\n\n\n\n\n\n\n\n\n
\n
Product Name<\/strong><\/p>\n<\/td>\n
\n
Calendar Year<\/strong><\/p>\n<\/td>\n
\n
Sales Amount<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
Mountain-100    Silver, 38<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n
\n
$98,599.71<\/p>\n<\/td>\n<\/tr>\n
\n
Mountain-100    Silver, 38<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n
\n
$98,599.71<\/p>\n<\/td>\n<\/tr>\n
\n
Mountain-100    Black, 44<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n
\n
$97,874.71<\/p>\n<\/td>\n<\/tr>\n
\n
Mountain-100    Black, 38<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n
\n
$94,499.72<\/p>\n<\/td>\n<\/tr>\n
\n
Mountain-100    Black, 42<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n
\n
$91,124.73<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 \tAs you can see, the data now includes only the three columns specified in the Summarize<\/strong><\/code> function, with the data sorted in descending order, based on the Sales<\/strong><\/code>Amount<\/strong><\/code> column. <\/p>\n
The Rollup Function<\/h1>\n
 \tYou’ve already gotten a taste of the Rollup<\/strong><\/code> function in the second article of this series. This time around, however, we’ll go into the function a little deeper and look at a couple other functions associated with Rollup<\/strong><\/code>. <\/p>\n
 \tThe Rollup<\/strong><\/code> function is used in conjunction with the Summarize<\/strong><\/code> function. The Summarize<\/strong><\/code> function groups data together by the columns specified when you call the function. The function then returns a table that can be used by other functions, wherever a parameter requires a table value. You can also use the function as the table expression in an Evaluate<\/strong><\/code> statement. <\/p>\n
 \tThe Summarize<\/strong><\/code> function supports the use of the Rollup<\/strong><\/code> function as part of the Summarize<\/strong><\/code> definition. The Rollup<\/strong><\/code> function adds roll-up rows to the returned table based on the columns used to group the data. The roll-up rows provide an additional layer of aggregation, above what you’ve already defined in your Summarize<\/strong><\/code> function. Let’s look at example to better understand how this works. In the following Evaluate<\/strong><\/code> statement, we use the Summarize<\/strong><\/code> function as the statement’s table expression: <\/p>\n
evaluate\n(\n   summarize\n   (\n      calculatetable\n      (\n         'Internet Sales',\n         'Product Category'[Product Category Name] = \"Accessories\"\t \n      ),\n      'Product'[Product Name],\n      rollup('Date'[Calendar Year]),\n      \"Total Sales\", format(sum('Internet Sales'[Sales Amount]), \"currency\")\n   )\n)\norder by\n   'Product'[Product Name],\n   'Date'[Calendar Year]<\/pre>\n
 \tThe first parameter in the Summarize<\/strong><\/code> function requires its own table or table expression as its value. In this case, I’ve used a CalculateTable<\/strong><\/code> function to create that expression. The function returns the Internet Sales<\/strong><\/code> table, but limits the rows returned to those with a Product Category Name<\/strong><\/code> value equal to Accessories<\/strong><\/code>. <\/p>\n
 \tThe second parameter value in the Summarize<\/strong><\/code> function specifies the Product Name<\/strong><\/code> column, which means the data will be grouped based on the values in that column. <\/p>\n
 \tThe third value specifies the Calendar Year<\/strong><\/code> column. As a result, each product will be grouped by the year it was sold. However, I’ve also included the Rollup<\/strong><\/code> function in this expression. That means an additional row will be returned for each product that rolls up the data into a total for all years. <\/p>\n
 \tThe last parameter value in the Summarize<\/strong><\/code> function is a calculated column named Total Sales<\/strong><\/code>. The column uses the Sum<\/strong><\/code> aggregate function to total the Sales Amount<\/strong><\/code> values for each grouping. I’ve also used the F<\/strong><\/code>ormat<\/strong><\/code> function to return the data in a currency format. The following table shows the results returned by the Evaluate<\/strong><\/code> statement: <\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Product Name<\/strong><\/p>\n<\/td>\n
\n
Calendar Year<\/strong><\/p>\n<\/td>\n
\n
Total Sales<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
All-Purpose    Bike Stand<\/p>\n<\/td>\n
<\/td>\n\n
$39,591.00<\/p>\n<\/td>\n<\/tr>\n
\n
All-Purpose    Bike Stand<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$18,921.00<\/p>\n<\/td>\n<\/tr>\n
\n
All-Purpose    Bike Stand<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$20,670.00<\/p>\n<\/td>\n<\/tr>\n
\n
Bike    Wash – Dissolver<\/p>\n<\/td>\n
<\/td>\n\n
$7,218.60<\/p>\n<\/td>\n<\/tr>\n
\n
Bike    Wash – Dissolver<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$3,044.85<\/p>\n<\/td>\n<\/tr>\n
\n
Bike    Wash – Dissolver<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$4,173.75<\/p>\n<\/td>\n<\/tr>\n
\n
Fender    Set – Mountain<\/p>\n<\/td>\n
<\/td>\n\n
$46,619.58<\/p>\n<\/td>\n<\/tr>\n
\n
Fender    Set – Mountain<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$19,408.34<\/p>\n<\/td>\n<\/tr>\n
\n
Fender    Set – Mountain<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$27,211.24<\/p>\n<\/td>\n<\/tr>\n
\n
Hitch    Rack – 4-Bike<\/p>\n<\/td>\n
<\/td>\n\n
$39,360.00<\/p>\n<\/td>\n<\/tr>\n
\n
Hitch    Rack – 4-Bike<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$16,440.00<\/p>\n<\/td>\n<\/tr>\n
\n
Hitch    Rack – 4-Bike<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$22,920.00<\/p>\n<\/td>\n<\/tr>\n
\n
HL    Mountain Tire<\/p>\n<\/td>\n
<\/td>\n\n
$48,860.00<\/p>\n<\/td>\n<\/tr>\n
\n
HL    Mountain Tire<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$20,300.00<\/p>\n<\/td>\n<\/tr>\n
\n
HL    Mountain Tire<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$28,560.00<\/p>\n<\/td>\n<\/tr>\n
\n
HL    Road Tire<\/p>\n<\/td>\n
<\/td>\n\n
$27,970.80<\/p>\n<\/td>\n<\/tr>\n
\n
HL    Road Tire<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$12,877.00<\/p>\n<\/td>\n<\/tr>\n
\n
HL    Road Tire<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$15,093.80<\/p>\n<\/td>\n<\/tr>\n
\n
Hydration    Pack – 70 oz.<\/p>\n<\/td>\n
<\/td>\n\n
$40,307.67<\/p>\n<\/td>\n<\/tr>\n
\n
Hydration    Pack – 70 oz.<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$16,771.95<\/p>\n<\/td>\n<\/tr>\n
\n
Hydration    Pack – 70 oz.<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$23,535.72<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tAs with the previous examples, the results shown here are only part of the returned data, but you can see that totals are returned for each product sold and year it was sold. You can also see the roll-up row for each product-the rows without a Calendar Year<\/strong><\/code> value. <\/p>\n
 \tThere might be times when you want to mark in some way when a row is a roll-up, other than just depending on blank values in the roll-up column. DAX includes the IsSubtotal<\/strong><\/code> function that you can use in conjunction with the Rollup<\/strong><\/code> function to create a column that indicates when a row is a roll-up. The IsSubtotal<\/strong><\/code> function returns a value of True<\/strong><\/code> if the row is a roll-up. Otherwise, the function returns False<\/strong><\/code>. The following example demonstrates how to use the IsSubtotal<\/strong><\/code> function by creating an additional column named Is<\/strong><\/code>Rollup<\/strong><\/code>: <\/p>\n
evaluate\n(\n   summarize\n   (\n      calculatetable\n      (\n         'Internet Sales',\n         'Product Category'[Product Category Name] = \"Accessories\"\n      ),\n      'Product'[Product Name],\n      rollup('Date'[Calendar Year]),\n      \"Total Sales\", format(sum('Internet Sales'[Sales Amount]), \"currency\"),\n      \"Is Rollup\", IsSubtotal('Date'[Calendar Year])\n   )\n)\norder by\n   'Product'[Product Name],\n   'Date'[Calendar Year]<\/pre>\n
 \tThe Evaluate<\/strong><\/code> statement is the same as the one shown in the preceding example, except for the addition of the Is Rollup<\/strong><\/code> column. Notice that the expression that defines the column includes the IsSubtotal<\/strong><\/code> function. When calling the function, you need to include the name of the column specified in the Rollup<\/strong><\/code> function, in this case the Calendar Year<\/strong><\/code> column. The Evaluate<\/strong><\/code> statement returns the following results: <\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Product Name<\/strong><\/p>\n<\/td>\n
\n
Calendar Year<\/strong><\/p>\n<\/td>\n
\n
Total Sales<\/strong><\/p>\n<\/td>\n
\n
Is Rollup<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
All-Purpose    Bike Stand<\/p>\n<\/td>\n
<\/td>\n\n
$39,591.00<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
All-Purpose    Bike Stand<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$18,921.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
All-Purpose    Bike Stand<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$20,670.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bike    Wash – Dissolver<\/p>\n<\/td>\n
<\/td>\n\n
$7,218.60<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Bike    Wash – Dissolver<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$3,044.85<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bike    Wash – Dissolver<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$4,173.75<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Fender    Set – Mountain<\/p>\n<\/td>\n
<\/td>\n\n
$46,619.58<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Fender    Set – Mountain<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$19,408.34<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Fender    Set – Mountain<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$27,211.24<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Hitch    Rack – 4-Bike<\/p>\n<\/td>\n
<\/td>\n\n
$39,360.00<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Hitch    Rack – 4-Bike<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$16,440.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Hitch    Rack – 4-Bike<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$22,920.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
HL    Mountain Tire<\/p>\n<\/td>\n
<\/td>\n\n
$48,860.00<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
HL    Mountain Tire<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$20,300.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
HL    Mountain Tire<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$28,560.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
HL    Road Tire<\/p>\n<\/td>\n
<\/td>\n\n
$27,970.80<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
HL    Road Tire<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$12,877.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
HL    Road Tire<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$15,093.80<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Hydration    Pack – 70 oz.<\/p>\n<\/td>\n
<\/td>\n\n
$40,307.67<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Hydration    Pack – 70 oz.<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$16,771.95<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Hydration    Pack – 70 oz.<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$23,535.72<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tThe result set now includes the Is<\/strong><\/code>Rollup<\/strong><\/code> column and is populated with True<\/strong><\/code> and False<\/strong><\/code> values, with each roll-up row receiving a value of True<\/strong><\/code>. <\/p>\n
 \tNow suppose that instead of grouping our data by product and then year sold, we group it by product category, then product subcategory, and finally the year sold, as shown in the following example: <\/p>\n
evaluate\n(\n   summarize\n   (\n      'Internet Sales',\n      'Product Category'[Product Category Name],\n      'Product Subcategory'[Product Subcategory Name],\n      rollup('Date'[Calendar Year]),\n      \"Total Sales\", format(sum('Internet Sales'[Sales Amount]), \"currency\"),\n      \"Is Rollup\", IsSubtotal('Date'[Calendar Year])\n   )\n)\norder by\n   'Product Category'[Product Category Name],\n   'Product Subcategory'[Product Subcategory Name],\n   'Date'[Calendar Year]<\/pre>\n
 \tAs in the previous examples, we’re rolling up our data based on the Calendar<\/strong><\/code>Year<\/strong><\/code> column. Consequently, the statement will provide a roll-up row for each category\/subcategory grouping, as shown in the following results: <\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Product    Category Name<\/strong><\/p>\n<\/td>\n
\n
Product    Subcategory Name<\/strong><\/p>\n<\/td>\n
\n
Calendar Year<\/strong><\/p>\n<\/td>\n
\n
Total Sales<\/strong><\/p>\n<\/td>\n
\n
Is Rollup<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Racks<\/p>\n<\/td>\n
<\/td>\n\n
$39,360.00<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Racks<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$16,440.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Racks<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$22,920.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Stands<\/p>\n<\/td>\n
<\/td>\n\n
$39,591.00<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Stands<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$18,921.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Stands<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$20,670.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bottles    and Cages<\/p>\n<\/td>\n
<\/td>\n\n
$56,798.19<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bottles    and Cages<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$23,280.27<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bottles    and Cages<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$33,517.92<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Cleaners<\/p>\n<\/td>\n
<\/td>\n\n
$7,218.60<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Cleaners<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$3,044.85<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Cleaners<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$4,173.75<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Fenders<\/p>\n<\/td>\n
<\/td>\n\n
$46,619.58<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Fenders<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$19,408.34<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Fenders<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$27,211.24<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Helmets<\/p>\n<\/td>\n
<\/td>\n\n
$225,335.60<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Helmets<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$92,583.54<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Helmets<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$132,752.06<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Hydration    Packs<\/p>\n<\/td>\n
<\/td>\n\n
$40,307.67<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Hydration    Packs<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$16,771.95<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Hydration    Packs<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$23,535.72<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Tires    and Tubes<\/p>\n<\/td>\n
<\/td>\n\n
$245,529.32<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Tires    and Tubes<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$103,259.76<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Tires    and Tubes<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$142,269.56<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
<\/td>\n\n
$9,952,759.56<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n
\n
$585,973.27<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n
\n
$1,562,456.76<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$3,989,638.48<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$3,814,691.06<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tThis level of roll-up is probably fine for some of your needs, but at times, you might find it handy to return a roll-up row for each category, as well as each category\/subcategory grouping. The easiest way to do that is to add the Product<\/strong><\/code>Subcategory<\/strong><\/code>Name<\/strong><\/code> column to the Rollup<\/strong><\/code> function, as shown in the following example: <\/p>\n
evaluate\n(\n   summarize\n   (\n      'Internet Sales',\n      'Product Category'[Product Category Name],\n      rollup('Product Subcategory'[Product Subcategory Name], 'Date'[Calendar Year]),\n      \"Total Sales\", format(sum('Internet Sales'[Sales Amount]), \"currency\"),\n      \"Is Rollup\", IsSubtotal('Date'[Calendar Year])\n   )\n)\norder by\n   'Product Category'[Product Category Name],\n   'Product Subcategory'[Product Subcategory Name],\n   'Date'[Calendar Year]<\/pre>\n
 \tWhen adding an additional column to the Rollup<\/strong><\/code> function, you must separate the column names with a comma, but that’s all you’ll need to do. As the following table shows, the results now include a roll-up row for each product category: <\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Product    Category Name<\/strong><\/p>\n<\/td>\n
\n
Product    Subcategory Name<\/strong><\/p>\n<\/td>\n
\n
Calendar Year<\/strong><\/p>\n<\/td>\n
\n
Total Sales<\/strong><\/p>\n<\/td>\n
\n
Is Rollup<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
<\/td>\n<\/td>\n\n
$700,759.96<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Racks<\/p>\n<\/td>\n
<\/td>\n\n
$39,360.00<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Racks<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$16,440.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Racks<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$22,920.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Stands<\/p>\n<\/td>\n
<\/td>\n\n
$39,591.00<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Stands<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$18,921.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Stands<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$20,670.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bottles    and Cages<\/p>\n<\/td>\n
<\/td>\n\n
$56,798.19<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bottles    and Cages<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$23,280.27<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bottles    and Cages<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$33,517.92<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Cleaners<\/p>\n<\/td>\n
<\/td>\n\n
$7,218.60<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Cleaners<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$3,044.85<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Cleaners<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$4,173.75<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Fenders<\/p>\n<\/td>\n
<\/td>\n\n
$46,619.58<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Fenders<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$19,408.34<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Fenders<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$27,211.24<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Helmets<\/p>\n<\/td>\n
<\/td>\n\n
$225,335.60<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Helmets<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$92,583.54<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Helmets<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$132,752.06<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Hydration    Packs<\/p>\n<\/td>\n
<\/td>\n\n
$40,307.67<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Hydration    Packs<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$16,771.95<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Hydration    Packs<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$23,535.72<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Tires    and Tubes<\/p>\n<\/td>\n
<\/td>\n\n
$245,529.32<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Tires    and Tubes<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$103,259.76<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Tires    and Tubes<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$142,269.56<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
<\/td>\n<\/td>\n\n
$28,318,144.65<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
<\/td>\n\n
$9,952,759.56<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n
\n
$585,973.27<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n
\n
$1,562,456.76<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$3,989,638.48<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$3,814,691.06<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 \tThe Summarize<\/strong><\/code> function supports one other related function worth mentioning: RollupGroup<\/strong><\/code>. When used in conjunction with the Rollup<\/strong><\/code> function, the RollupGroup<\/strong><\/code> function prevents partial subtotals from being included in the result set. Let’s look at another example to demonstrate how this works. In the following Evaluate<\/strong><\/code> statement, I’ve added the RollupGroup<\/strong><\/code> function as part of the Rollup<\/strong><\/code> function: <\/p>\n
evaluate\n(\n   summarize\n   (\n      'Internet Sales',\n      'Product Category'[Product Category Name],\n      rollup(rollupgroup('Product Subcategory'[Product Subcategory Name], 'Date'[Calendar Year])),\n      \"Total Sales\", format(sum('Internet Sales'[Sales Amount]), \"currency\"),\n      \"Is Rollup\", IsSubtotal('Date'[Calendar Year])\n   )\n)\norder by\n   'Product Category'[Product Category Name],\n   'Product Subcategory'[Product Subcategory Name],\n   'Date'[Calendar Year]<\/pre>\n
 \tThe column names are passed into the RollupGroup<\/strong><\/code> function, and that function is passed to the Rollup<\/strong><\/code> function. As to be expected, the statement now returns slightly different results: <\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Product    Category Name<\/strong><\/p>\n<\/td>\n
\n
Product Subcategory    Name<\/strong><\/p>\n<\/td>\n
\n
Calendar Year<\/strong><\/p>\n<\/td>\n
\n
Total Sales<\/strong><\/p>\n<\/td>\n
\n
Is Rollup<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
<\/td>\n<\/td>\n\n
$700,759.96<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Racks<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$16,440.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Racks<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$22,920.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Stands<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$18,921.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bike    Stands<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$20,670.00<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bottles    and Cages<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$23,280.27<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Bottles    and Cages<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$33,517.92<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Cleaners<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$3,044.85<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Cleaners<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$4,173.75<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Fenders<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$19,408.34<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Fenders<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$27,211.24<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Helmets<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$92,583.54<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Helmets<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$132,752.06<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Hydration    Packs<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$16,771.95<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Hydration    Packs<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$23,535.72<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Tires    and Tubes<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$103,259.76<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
Tires    and Tubes<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$142,269.56<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
<\/td>\n<\/td>\n\n
$28,318,144.65<\/p>\n<\/td>\n
\n
True<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n
\n
$585,973.27<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n
\n
$1,562,456.76<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$3,989,638.48<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
Mountain    Bikes<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$3,814,691.06<\/p>\n<\/td>\n
\n
False<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tA roll-up row is included for each category, but not for the subcategories. As you can see, DAX provides a number of ways to roll up your data so your results include exactly the information you need. <\/p>\n
The Crossjoin Function<\/h2>\n
 \tThe last function that we’ll look at is Crossjoin<\/strong><\/code>. You use a Crossjoin<\/strong><\/code> function to join two or more tables in order to return a Cartesian <\/em>product<\/em>,<\/em> a result set in which each row in each table is joined with each row in each other table. The result set is the product of the number of rows from each table. For example, if one table contains five rows and a second table contains seven rows, the Cartesian product for those two tables will contain 35 rows. <\/p>\n
 \tThis will all become clearer as we work through our examples. But first, take a look at the following Evaluate<\/strong><\/code> statement, in which we retrieve data from the Internet Sales<\/strong><\/code> table: <\/p>\n
evaluate\n(\n   summarize\n   (\n      'Internet Sales',\n      'Product Category'[Product Category Name],\n      'Date'[Calendar Year],\n      \"Total Sales\", format(sum('Internet Sales'[Sales Amount]), \"currency\")\n   )\n)\norder by\n   'Product Category'[Product Category Name],\n    'Date'[Calendar Year]<\/pre>\n
 \tThe statement contains nothing that you haven’t seen before. It groups data based on the Product<\/strong><\/code>Category<\/strong><\/code> and Calendar<\/strong><\/code>Year<\/strong><\/code> columns and provides the sales totals for each group, as shown in the following results: <\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n
\n
Product    Category Name<\/strong><\/p>\n<\/td>\n
\n
Calendar Year<\/strong><\/p>\n<\/td>\n
\n
Total Sales<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$293,709.71<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$407,050.25<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n
\n
$3,266,373.66<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n
\n
$6,530,343.53<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$9,359,102.62<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$9,162,324.85<\/p>\n<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$138,247.97<\/p>\n<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$201,524.64<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tThe Evaluate<\/strong><\/code> statement returns just what we’d expect. However, what’s important about these results are not what they include, but what is missing. For example, the Product Category<\/strong><\/code> column in the source table includes the value Components<\/strong><\/code>, but that value isn’t included in the results because no sales are associated with that category. In addition, years are displayed only if they have sales associated with them. For instance, the results include information about the Accessories category for 2007 and 2008, but no other years are listed. <\/p>\n
 \tOften, we’ll want our results to be more complete than what we’re able to generate with a simple Summarize<\/strong><\/code> function and for that we need the Crossjoin<\/strong><\/code> function. It returns a Cartesian product, and we can use that data to generate more complete results. <\/p>\n
 \tLet’s look at an example to give you an idea of how the Crossjoin<\/strong><\/code> function works. In the following Evaluate<\/strong><\/code> statement, I use the Crossjoin<\/strong><\/code> function as the statement’s table expression: <\/p>\n
evaluate\n(\n   crossjoin\n   (\n      values('Product Category'[Product Category Name]),\n      values('Date'[Calendar Year])\n   )\n)\norder by\n   'Product Category'[Product Category Name],\n   'Date'[Calendar Year]<\/pre>\n
 \tI pass two arguments into the Crossjoin<\/strong><\/code> function. Each argument must return a table. In this case, I use the Values<\/strong><\/code> function for each argument in order to return a one-column table of distinct values. The first Values<\/strong><\/code> function returns a list of values from the Product Category Name<\/strong><\/code> column, and the second Values<\/strong><\/code> function returns a list of values from the Calendar Year<\/strong><\/code> column. (These are the same two columns used to group data in the previous example.) The Evaluate<\/strong><\/code> statement returns the results shown in the following table: <\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Product    Category Name<\/strong><\/p>\n<\/td>\n
\n
Calendar Year<\/strong><\/p>\n<\/td>\n<\/tr>\n
<\/td>\n\n
2005<\/p>\n<\/td>\n<\/tr>\n
<\/td>\n\n
2006<\/p>\n<\/td>\n<\/tr>\n
<\/td>\n\n
2007<\/p>\n<\/td>\n<\/tr>\n
<\/td>\n\n
2008<\/p>\n<\/td>\n<\/tr>\n
<\/td>\n\n
2009<\/p>\n<\/td>\n<\/tr>\n
<\/td>\n\n
2010<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2009<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2010<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2009<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2010<\/p>\n<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2009<\/p>\n<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2010<\/p>\n<\/td>\n<\/tr>\n
\n
Components<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n<\/tr>\n
\n
Components<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n<\/tr>\n
\n
Components<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n<\/tr>\n
\n
Components<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n<\/tr>\n
\n
Components<\/p>\n<\/td>\n
\n
2009<\/p>\n<\/td>\n<\/tr>\n
\n
Components<\/p>\n<\/td>\n
\n
2010<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tEach row in each table has been joined with each row in the other table. For example, the Accessories<\/strong><\/code> value in the Product Category Name<\/strong><\/code> column is matched with each year in the Calendar Year<\/strong><\/code> column. The reason that there are blank values in the Product Category Name<\/strong><\/code> column is because the source data-the Product Category<\/strong><\/code> table in the tabular database-includes a blank member, and that blank member is matched with each year, just like the other Product Category Name<\/strong><\/code> values. Because the Product Category<\/strong><\/code> column contains five distinct values, and the Calendar Year<\/strong><\/code> column contains six distinct values, our query returns 30 rows. <\/p>\n
 \tNow that we know how to use the Crossjoin<\/strong><\/code> function to produce the Cartesian product, we can include it in our Evaluate<\/strong><\/code> statement to return more meaningful data. The following example uses the Crossjoin<\/strong><\/code> function as the table expression within the Summarize<\/strong><\/code> function: <\/p>\n
evaluate\n(\n   summarize\n  (\n     crossjoin\n     (\n        values('Product Category'[Product Category Name]),\n        values('Date'[Calendar Year])\n     ),\n     'Product Category'[Product Category Name],\n     'Date'[Calendar Year],\n     \"Total Sales\", format(sum('Internet Sales'[Sales Amount]), \"currency\")\n  )\n)\norder by\n    'Product Category'[Product Category Name],\n    'Date'[Calendar Year]<\/pre>\n
 \tThe Summarize<\/strong><\/code> function is now working with a Cartesian<\/strong><\/code> product as its table. We can then group the data based on the Product<\/strong><\/code>Category<\/strong><\/code>Name<\/strong><\/code> column and the Calendar<\/strong><\/code>Year<\/strong><\/code> column, both of which are including in the Cartesian product. Now the Evaluate<\/strong><\/code> statement returns the following results: <\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Product    Category Name<\/strong><\/p>\n<\/td>\n
\n
Calendar Year<\/strong><\/p>\n<\/td>\n
\n
Total Sales<\/strong><\/p>\n<\/td>\n<\/tr>\n
<\/td>\n\n
2005<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
<\/td>\n\n
2006<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
<\/td>\n\n
2007<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
<\/td>\n\n
2008<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
<\/td>\n\n
2009<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
<\/td>\n\n
2010<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$293,709.71<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$407,050.25<\/p>\n<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2009<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Accessories<\/p>\n<\/td>\n
\n
2010<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n
\n
$3,266,373.66<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n
\n
$6,530,343.53<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$9,359,102.62<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$9,162,324.85<\/p>\n<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2009<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Bikes<\/p>\n<\/td>\n
\n
2010<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
\n
$138,247.97<\/p>\n<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
\n
$201,524.64<\/p>\n<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2009<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Clothing<\/p>\n<\/td>\n
\n
2010<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Components<\/p>\n<\/td>\n
\n
2005<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Components<\/p>\n<\/td>\n
\n
2006<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Components<\/p>\n<\/td>\n
\n
2007<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Components<\/p>\n<\/td>\n
\n
2008<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Components<\/p>\n<\/td>\n
\n
2009<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n
\n
Components<\/p>\n<\/td>\n
\n
2010<\/p>\n<\/td>\n
<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 \tAs you can see, all the data is included in the results, whether or not any sales were made for a particular category in a particular year. This way, you know you’re returning the complete results, which can often give you a better picture of the data-and point to where potential problems might exist. <\/p>\n
DAX Statistical Functions<\/h1>\n
 \tYou should now have a good sense of how to use the Row<\/strong><\/code>, Topn<\/strong><\/code>, Rollup<\/strong><\/code>, and Crossjoin<\/strong><\/code> functions, along with several of the aggregate statistical functions. Once you know the basics of how to use them, you should be able to apply them to other situations. DAX also supports a number of other statistical functions, as well as additional functions types, such as date and time functions, filter functions, and logical functions. Be sure to check out the TechNet article “DAX Function Reference<\/a>” for information about those other functions as well as additional details about the functions we’ve covered. <\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
Following on from his first four articles on using Data Analysis Expressions (DAX) with tabular databases, Robert Sheldon dives into some of the DAX statistical functions available, demonstrating which are the most useful and examples of how they work. …<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4168,4170,5872,4658,4150,5131,5296,5873],"coauthors":[],"class_list":["post-1699","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database","tag-database-administration","tag-dax","tag-functions","tag-sql","tag-ssas","tag-statistics","tag-tabular-model"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1699","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1699"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1699\/revisions"}],"predecessor-version":[{"id":92235,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1699\/revisions\/92235"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1699"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1699"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1699"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1699"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}