How to Write DAX Summary Queries In Excel Using PowerPivot: The SUMMARIZE Function

In the previous segment, we saw how to write DAX statements in Excel to query Power Pivot Data. In this segment, we will take a first look at the SUMMARIZE function. While the DAX queries discussed here could be executed in the Management Studio against an Analysis Services tabular mode database, here we will be running them using the Excel QueryTable object. If you would like to download the workbook developed in the previous section, you can do so here. You can also download a text file containing the example queries in this post. This is a flat text file, but has the extension “mdx” so it will be recognized by the Management Studio should you wish to run these queries against a tabular mode database rather than the Power Pivot file.

Although Power Pivot pivot tables provide great functionality for the exploration of data, there are often times when an analyst needs to assume assume control and provide summary data reports directly. In the DAX language, it is the SUMMARIZE function which provides the flexibility of querying Power Pivot data. Indeed, SUMMARIZE is very much the “SELECT” statement for tabular mode data.

As we saw in the first installment, it is possible to write DAX queries in Excel to pull data from the workbooks own Power Pivot tables. The example queries here work with the sample Excel workbook xxx. Of course, the example queries shown here will also work in the Management Studio when querying a tabular mode Analysis Services database.

Here we have a query that is directly analogous to a simple SELECT statement. We haves used SUMMARIZE only to select the columns to be displayed and include a sort order. The ORDER BY clause is identical to that of SQL. Note that the first argument to SUMMARIZE is the base table name, the succeeding arguments are column names.

EVALUATE
SUMMARIZE (
Product,
Product[EnglishProductName],
Product[DealerPrice]
)
ORDER BY Product[DealerPrice] DESC

Results of simple SUMMARIZE query
Results of simple SUMMARIZE query

 

What About Joins?

Joining data from different tables in a relational database is usually necessary for all but the simplest queries. In DAX, however, there is no specific JOIN syntax like there is in SQL. As long the tables have an active relationship defined in the Power Pivot database, columns from other tables are simply added to the SUMMARIZE function. Here is an example similar to the example provided by MSDN. Note that in this case, however, date information is stored in a table called “Date”. Because “Date” is a keyword in DAX, we must provide single quote characters around the table name ‘Date’. We can always use single quotes around table names if we wish, and many people do. Here, however, the quotes are required.

EVALUATE
SUMMARIZE (
ResellerSales,
‘Date'[CalendarYear],
ProductCategory[EnglishProductCategoryName],
“Sales Amount (USD)”SUM ( ResellerSales[SalesAmount] ),
“Discount Amount (USD)”SUM ( ResellerSales[DiscountAmount] )
)

 

Sales by Category and Year
Sales by Category and Year

 

How about subtotals like the SQL statement WITH ROLLUP?

ROLLUP queries include subtotals along the first dimension of grouped query results. In the previous example, we calculated the sums for each component for each  year. The ROLLUP function provides subtotals for each individual year as well as a grand total for the entire set of rows. Notice that in the DAX statement, the first column in the ROLLUP list, in this case CalendarYear, is the dimension along which the subtotals are taken

EVALUATE
SUMMARIZE (
ResellerSales,
ROLLUP (
'Date'[CalendarYear],
ProductCategory[EnglishProductCategoryName]
),
"Sales Amount (USD)"SUM ( ResellerSales[SalesAmount] ),
"Discount Amount (USD)"SUM ( ResellerSales[DiscountAmount] )
)

Sales by Category and Year including ROLLUP Subtotals
Sales by Category and Year including Subtotals

 

In the following ROLLUP example, we see that the EnglishProductCategoryName column is the first argument to ROLLUP, so at the end of the results we see subtotals over each category.

EVALUATE
SUMMARIZE (
ResellerSales,
ROLLUP (
ProductCategory[EnglishProductCategoryName],
'Date'[CalendarYear]
),
"Sales Amount (USD)"SUM ( ResellerSales[SalesAmount] ),
"Discount Amount (USD)"SUM ( ResellerSales[DiscountAmount] )
)

 

ROLLUP by Category Rather than Year
ROLLUP by Category Rather than Year

 

Is there a HAVING clause?

No. The familar DAX function FILTER can be used with SUMMARIZE just as elsewhere. In the following example, we note that the column name assigned to the SUM, i,e, “Sales”, is used as the last argument to the FILTER function.

The following query will return results for every calendar year.

EVALUATE
SUMMARIZE (
InternetSales,
'Date'[CalendarYear],
"Sales"SUM ( InternetSales[SalesAmount] )
)
ORDER BY 'Date'[CalendarYear]

Results from using FILTER function with SUMMARIZE
Results before using FILTER function with SUMMARIZE

 

What if we wish to see only those years for which the sum exceeds $8 Million? The first argument to the filter function is the SUMMARIZE statement we just created. The second argument is the criterion, in this case Sales > 8000000. Note that there is no “Sales” column in the underlying table; Sales is simply the name defined in the SUMMARIZE statement.

EVALUATE
FILTER (
SUMMARIZE (
InternetSales,
‘Date'[CalendarYear],
“Sales”SUM ( InternetSales[SalesAmount] )
),
[Sales] > 8000000
)
ORDER BY ‘Date'[CalendarYear]

Results after applying FILTER function with SUMMARIZE
Results after applying FILTER function with SUMMARIZE

 

The Bottom Line:

We have seen some fundamental uses of the SUMMARIZE function to create summary queries of data stored in Excel Power Pivot. There are a few subtleties and a few potential issues we have not yet addressed. As is often the case, these relate to a proper understanding of execution context in DAX queries. We’ll examine these topics in detail in a future post.

Please to not hesitate to drop a note if you have any questions.

PS – If you would like to learn more about using PowerPivot for Excel & how to analyse date with the DAX language, check out the Learning Tree course – PowerPivot for Excel: Mining Data for Business Intelligence.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.