In this Level of the Stairway to PowerPivot and DAX series, we introduce the DAX SUM() and SUMX() functions, which support, respectively, the need to add up the numbers of a column, or the need to return the sum of an expression, evaluated for each row in a table. Both functions perform aggregations: SUM(), typically, in the role of a “measure function” (we get the effect of SUM() when we drag a number into the Value drop zone of a PivotTable, by default), and SUMX() in the role of an iterator, which, as we shall see in the sections that follow, isolates a table (or an expression that returns a table), and then aggregates a columnar subset of that table.

As a part of our introduction, we will discuss ways in which we can employ SUM()and SUMX(); we'll look at each function in standalone fashion, as well as in situations where we combine it with other functions, to achieve a result similar to those we might seek to achieve for clients, employers or peers in our own environments. During our exploration of the SUM() and SUMX() functions, we will:

• Discuss DAX function / iterator function pairs, in general;
• Examine the syntax involved in exploiting each;
• Undertake illustrative examples of the uses of each function in practice exercises;
• Briefly discuss the results datasets we obtain in each of the practice examples.

As is standard procedure in my introductions to DAX functions throughout this Stairway series, we will examine the output we obtain, in this specific Level employing SUM() and SUMX() via calculated columns we construct within the PowerPivot window in the practice session that follows. We will further create calculations that put both SUM() and SUMX() to work in PivotTable measures, to further scrutinize the behavior that we have examined in the calculated column created in the PowerPivot window. Along the way, as appropriate, we will follow another path we have taken consistently within the Levels of this series: we will compare and contrast differences in use and behavior of the functions in calculated columns and calculated members in general, suggesting criteria to consider in choosing which of these calculation types to employ to meet the business requirements we encounter.

Preparation for the Levels of the Stairway to PowerPivot and DAX Series

To obtain the most benefit from the Levels of the Stairway to PowerPivot and DAX series, you will need to have installed the appropriate PowerPivot for Excel 2010 add-on on your machine. (You can also use Microsoft Office Excel 2013 to complete most steps of the levels of this Stairway, although naming conventions and screen appearances might differ a little from what you see in the articles of this series). In many cases, you will also need to have installed SQL Server 2008 or above (including Analysis Services) and the respective relational and Analysis Services samples on your local machine, or have the client portions of each on your machine, with access to the rest on a server. To perform all steps of preparation for the Levels of Stairway to PowerPivot and DAX, see the installation section of the charter Level of this series, Level 1: Getting Started with PowerPivot and DAX.

Preparation for the Practice Exercises in this Level

Once we've gotten the PowerPivot for Excel 2010 add-in (or have installed and configured Excel 2013), SQL Server 2008 (or above) and the samples noted above installed, we're ready to perform the practice exercises within this Level. Importing the sample database and taking other preparatory steps are critical to providing the environment required to successfully complete this Level, and should be performed before beginning.

Because we will work with different data sources throughout this series, the steps of environment preparation will be repeated in various Levels – primarily to make each Level a freestanding session that can be completed independently of other Levels, so that you can focus upon gaining exposure to the specific skills required to meet your own immediate requirements efficiently. Where appropriate, references to functions, procedures and other details will be supplied, but the objective of each Level is to focus upon one or more specific DAX functions and / or PowerPivot / PivotTable procedures to satisfy needs that mirror requirements we may encounter in our own business environments.

Let's open PowerPivot and import our data by taking the steps below. This will put us in position to begin learning the material in this Level.

1. From the Start menu, select Microsoft Excel.
2. Above the Excel ribbon, within the Home tab of a new worksheet, click the PowerPivot tab, as shown.

Illustration 1: Click the PowerPivot Tab within a New Worksheet…

The PowerPivot ribbon appears.

3. Click the PowerPivot Window launch button, appearing at the left of the newly appearing PowerPivot toolbar, as shown.

Illustration 2: The PowerPivot Window Launch Button on the PowerPivot Ribbon…

The PowerPivot window, containing its own ribbon, opens atop the existing Excel spreadsheet, assuming the name of the workbook as its own name, as depicted below.

Illustration 3: The PowerPivot Window, Associated with the Workbook, Appears

As we noted in Level 1: Getting Started with PowerPivot and DAX, it is in the PowerPivot window that we load and prepare the data with which we will be working (or will continue working, if data is already added to the workbook). We will typically build a relational model here (even when we import other-than-relational sources, such as Analysis Services data, text files, .RSS feeds, and so forth). As we also noted in Level 1, the PowerPivot window displays the tables on individual, tabbed sheets. This is the central place where we import tables, create relationships, maintain column data types and formats, and view the data that underlies our data model, among other actions.

Next, we'll designate a source from which to import data.

4. Click the From Database button on the Home tab of the PowerPivot window.
5. Select From SQL Server on the drop-down menu that appears next.

Illustration 4: Select “From SQL Server” in the From Database Dropdown

The Table Import Wizard dialog opens next.

  AdventureWorksDW2008R2

6. In the top input box, titled Friendly connection name, type (or copy and paste) the following:
7. Click the selector (downward pointing arrow) on the right side of the box titled Server name.

PowerPivot begins a scan of the machine to detect, and return to the selector, the available server choices.

8. Select the appropriate server for your local environment, or type in the server name / “localhost,” as appropriate.
9. Enter the authentication as required for the local environment (ideally selecting Use Windows Authentication).
10. Select AdventureWorksDW2008R2 using the dropdown selector to the right of the box titled, Database name, at the bottom of the dialog.

The Table Import Wizard dialog, with our input, appears similar to that depicted below.

Illustration 5: The Table Import Wizard Dialog, with Our Input

11. Click the Test Connection button underneath the Database name selector.

A message box appears, indicating that the test connection has succeeded.

Illustration 6: “Test Connection Succeeded”

12. Click OK to dismiss the message box.
13. Click the Next button at the bottom of the Table Import Wizard dialog.
14. On the dialog that appears next, labeled Choose How to Import the Data, leave the radio button at its default of Select from a list of tables and views to choose the data to import, as shown.

Illustration 7: Choose “Select from a list of tables…” Option for Data Import

15. Click the Next button.

PowerPivot loads the tables and views from the AdventureWorksDW2008R2 database into the Select Tables and Views page of that appears next.

16. Select each of the tables listed in Table 1 by clicking the checkbox to the immediate left of the respective table listing in the Source Table column of the dialog, and changing its name, via the Friendly Name in the dialog.

Table 1: “Select Table and Views” Dialog Input

The Select Tables and Views dialog appears, as partially shown, with the tables listed selected and designated with their new names.

Illustration 8: Selecting Tables (Partial View)…

The idea in these immediate steps, once again, is to import enough information into our model to allow us to do some illustrative analysis surrounding the business activities conducted by our hypothetical client, the Adventure Works Cycles organization.

17. Click the Finish button.

The import process runs, and then we see a “Success” message, complete with a Details list that indicates population by our choices.

Illustration 9: Success is Indicated, Along with a List of Tables Imported

18. Click Close.

The Table Import Wizard is dismissed, and we arrive at the PowerPivot window once again, where we see the imported data as partially depicted.

Illustration 10: Imported Data, with a Tab for Each Table (Partial View)

Note that a tab for each imported table has been created in the PowerPivot window (the tabs appear in the bottom left of the window, as seen above). What we are now seeing are not Excel tables / spreadsheets, but a view of the efficiently compressed columnar database that PowerPivot uses to store imported tables in memory. For more detailed information about the PowerPivot window itself, please see Level 1: Getting Started with PowerPivot and DAX.

19. Click the selector underneath the Quick Access Toolbar (assuming it is in the default, above-the-ribbon position), and to the immediate left of the Home tab in the ribbon.
20. Select Save As from the dropdown menu that appears, as shown in Illustration 11.

Illustration 11: Select Save As from the Selector within the PowerPivot Window…

20. Save the worksheet as ST_DAX06-1.xlsx, placing it in a convenient location.
21. Leave the PowerPivot Window open for the practice session below.

We are ready, at this stage, to get some hands-on exposure to a couple of DAX functions, SUM() and SUMX(). We will also revisit functions that we introduced in previous levels, while continuing to build experience with the general use of PowerPivot for Excel to meet business requirements, in the sections that follow.

The SUM() Function

Introduction

According to the TechNet Library, the DAX SUM() function ”returns the sum of a set of values contained in a specified field on a query.” SUM() is an “aggregate” member of the Math and Trig function group, as displayed in the formula bar for PowerPivot and PowerPivot PivotTables, many of the members of which are identical to functions in Excel. These functions are often used in combination with other DAX functions.

As most of us have come across SUM() in standard Excel, it is common knowledge that it is a relative “cornerstone” function. When we drag a field into a PivotTable (either a “classic” PivotTable or a PowerPivot PivotTable) Values drop zone, the default summary that is applied is Sum, as we can clearly see in the default naming of the example, YearlyIncome, which I've dragged into the Values drop zone depicted, and where its default name has become Sum of YearlyIncome. I can also verify this by right clicking upon the newly created Sum of YearlyIncome value, selecting Summarize By (whereby I can change the type of aggregation of a given Value), and noting that the Sum option is checked as being in force.

Illustration 12: Sum is the Default Treatment of a Field Added to the Values Drop Zone

SUM() simply adds the numbers residing in a column, and returns a number. As a part of gathering numbers for the total presented, a blank is returned for any rows containing non-numeric values (meaning that the effect for a given “empty” is the same as if it were a zero in the overall aggregation). It is significant to note that the SUM() function returns all numbers in a specified column – if a need exists to filter the values that we are summing, we can specify an expression, via the SUMX() function (discussed in its own section of this Level), of the range over which to sum.

We will explore the syntax for the SUM() function after a brief discussion in the next section. We will gain some hands-on exposure to its use, within practice examples constructed to support hypothetical business needs, in the Practice section later in this Level. This will allow us to activate what we explore in the Discussion and Syntax sections.

Discussion

To restate our initial explanation of its operation, the SUM() function returns an aggregation of all the values in a given column. The output of SUM() is a numerical value, and the function can be used with equal utility within PowerPivot calculated columns or PivotTable measures. SUM() is designed to create subtotals and totals, and is very often employed within other DAX function.

Syntax

Syntactically, in using the SUM() function to return a subtotal or total, we specify the value we wish to test within the parentheses to the right of the SUM keyword. As an illustration, we cite the use of SUM() within the Reseller Sales tab of the PowerPivot window within which we have been working. Let's say we employ SUM(), via a calculated column, in the following manner to add all numbers resident in the ProductStandardCost column:

  =SUM('Reseller Sales'[ProductStandardCost])

Were we to examine the new calculated column on the tab, we would see something like this (I've inserted the calculated column in the tab to be adjacent to the ProductStandardCost column, from which it is derived):

Illustration 13: Calculated Column Using SUM() on the Reseller Sales Tab (Partial View)

As is obvious, within the partial view we see above, the calculation returns the desired outcome, based upon the addition of all the values contained in the ProductStandardCost column.

The SUMX() Function

Introduction

According to the TechNet Library, the DAX SUMX() function ”returns the sum of an expression evaluated for each row in a table.” Like SUM(), SUMX() is an “aggregate” member of the Math and Trig function group, as displayed in the formula bar for PowerPivot and PowerPivot PivotTables, many of whose members, as we have noted earlier, are identical to functions in Excel. SUMX(), as we shall see, is, by its very nature, used in combination with other DAX functions. Like other DAX functions with an “X” suffix, SUMX() is an iterator function.

Iterator functions typically contain a table expression (the first parameter specified within the function), for which a calculation, based upon an expression specified by a second parameter, is performed iteratively for each row of the table. The ultimate result is generated by the application of the respective aggregation function (including SUM, AVERAGE, COUNTA, MAX, MIN, and COUNT) to the dataset returned by the first and second expressions. We will examine the behavior of other DAX “X” functions in independent examinations of each of the respective function / x-function pairs that follow this review of the SUM() and SUMX() functions.

SUMX() is useful anytime we want to filter a table / column we specify, based upon criteria we specify in a second expression, and then return a sum of all the values in the resulting column. If we do not need to filter the column, we use the DAX SUM() function, which is similar to the Excel function of the same name, except that it references a column.

We will briefly discuss the SUMX() function in the section that follows, after which we will explore SUMX() from a syntax perspective. We will then activate what we explore in the Discussion and Syntax sections as we gain hands-on exposure to its use, within practice examples constructed to support hypothetical business needs.

Discussion

To restate our initial explanation of its operation, the SUMX() function iterates through a specified table expression, performing a calculation for each row, and then sums the respective results obtained for each row. Only the numbers in the resulting column are summed. Blanks, text, and logical values are ignored.

Let's look at some syntax illustrations to further clarify the operation of SUMX().

Syntax

Syntactically, in using the SUMX() function to return the sum of an expression applied to each of a specified table's rows, the table expression (first parameter of the function), and the expression that we seek to apply to each row of the table (second parameter of the function), are specified, within parentheses, to the right of the SUMX keyword. The general syntax is shown in the following string:

SUMX(<table>, <expression>)

Putting SUMX(), or any of the “X iterator” functions, for that matter, to work is intuitive, once we grasp its purpose. When using the function to return the summary of the results of the action of the expression (“<expression>” in the syntax string above) upon each of the rows of the specified table (“<table>” in the syntax string above), we simply enclose the table and expression to the right of the SUMX keyword, as we have noted. The SUM function is then applied to the values returned for the respective rows, resulting in a single value that represents an aggregation of those values.

As an example, let's get a feel for the general use of SUMX() within the Reseller Sales tab of the PowerPivot window, where we have performed the imports described in the Preparation section above. Let's say we want to put SUMX() to use in a scenario where we wish to do something along the lines of this:

=SUMX('Reseller Sales', [TaxAmt])

This calculates the Tax Amount for the respective sale for each row in the Reseller Sales table, and then returns the sum of all these Tax Amount values. A partial view of the results of this calculation appears as shown:

Illustration 14: SUMX() Results: Total of the Tax Amount Column for the Reseller Sales Tab (Partial View)

We note, within the partial view above, that the calculation returns the desired sum for the criteria specified, all the values in the TaxAmt column.

We'll get some hands-on practice with SUM() and SUMX() in the sections that follow.

Practice

Let's reinforce our understanding of the basics by putting the SUM() and SUMX() functions to work within the definition of some calculations. We'll do this much as we have in the other Levels of the Stairway to PowerPivot and DAX series, first via calculated columns within a tab of the PowerPivot window, and then via calculated measures within a PivotTable of a worksheet within the associated Excel workbook.

We will examine the use of each of the SUM() and SUMX() functions in combination, where useful, with functions we have introduced in earlier Levels of this series. The intent, as in all the practice sessions we undertake together within this series, is to demonstrate the operation of the functions we examine in a straightforward, memorable manner. We will turn to the Excel worksheet we prepared earlier as a platform from which to construct and execute the DAX we examine, and to view the results we obtain.

PowerPivot Calculated Columns

Let's begin with a straightforward example: Let's say that we wish to perform a simple summation to obtain the total Sales Amount on the Internet Sales tab within the PowerPivot data sheet view that we established in our preparation steps earlier.

To do so, we will take the following steps:

1. Return to the worksheet we created in the Preparation for the Practice Exercises in this Level section above, ensuring that the PowerPivot Window is open, where we left it earlier.
2. Click the Internet Sales tab.

Illustration 15: Click the Internet Sales Tab…

3. Click the heading of the column labeled “TaxAmt,” which appears to the immediate right of the SalesAmt column on the Internet Sales tab, as shown.

Illustration 16: Click the Heading of the Column Labeled “TaxAmt”

4. Right-click the column label for the TaxAmt column.
5. Select Insert Column from the context menu that appears.

Illustration 17: Click Insert Column on the Context Menu…

A new calculated column, labelled CalculatedColumn1, appears.

Illustration 18: A New Calculated Column Appears…

6. Select the function button (“fx”) to the left of the formula bar next.

Illustration 19: Select the Function (fx) Button…

The Insert Function dialog appears.

7. Click the downward pointing arrow on the selector appearing atop the dialog under the Select a category label.

The following DAX function categories appear:

• All
• Date & Time
• Math & Trig
• Statistical
• Text
• Logical
• Filter
• Information
• Parent-Child

8. Select the Math & Trig category, on the Select a category dropdown, as depicted.

Illustration 20: Insert Function Dialog with Expanded Category Selector

9. In the Select a function list that populates for the Math & Trig category, select the SUM() function, as shown.

Illustration 21: Select the SUM() Function

10. Click OK to confirm the selection and dismiss the Insert Function dialog.

The SUM keyword, proceeded by the “=” operator and followed by a left parenthesis ( “(“ ), appears in the formula bar, with a function usage tooltip appearing just underneath the function.

Illustration 22: The Beginning of the SUM() Function, along with Tooltip, Appears

We will first create a simple calculation that generates a sum of the entire Sales Amount column of the Internet Sales tab, which we will name “Total Internet Sales.”

11. Following the left parenthesis inserted by our selection of SUM, begin to type in the word “InternetSales.”
12. Scroll down and select ‘Internet Sales'[SalesAmount] within the dropdown list to double-click / select the column, as depicted.

Illustration 23: Select the ‘Internet Sales'[SalesAmount] Column…

Once the selector is dismissed, and =SUM('Internet Sales'[SalesAmount] appears in the formula bar, type a right parenthesis ( “ ) ) at the current end of the syntax to close the SUM() function.

The syntax, at this point, should be as shown here:

  =SUM('Internet Sales'[SalesAmount])

14. Once the syntax shown above appears in the formula bar, press the ENTER key.

The new calculated column is populated, in every row, by the total of all values in the Sales Amount column of the Internet Sales tab. Let's name the column to distinguish it from the column upon which it is based, at this juncture.

15. Right-click the new column header label (currently at its default of “CalculatedColumn1”), and select Rename Column from the cascading menu that appears.

Illustration 24: Renaming the Column to its Intended Name

16. Name the newly added column “Total Internet Sales” to provide a unique label.

The newly-named column, with updated formula results, appears as partially depicted below.

Illustration 25: The Results Appear in Each Row of the New Column (Partial View)

We thus see how we can employ SUM() to deliver a simple total within the PowerPivot window. Next we'll take a look at the same operation, in a similar scenario, using SUMX().

Like SUM(), SUMX() is also a Math & Trig function, and the” X-function” version of SUM(). As is probably evident at this point, the SUM() function, like other functions in both Excel and in DAX, is limited to the use of a single column as an argument. The X-function counterparts (an example being SUMX() ) to several such “standard” functions (the respective example being SUM(), in the present case) enable us to construct arguments with multiple columns.

Let's look at a simple example that shows how we can use SUMX() to aggregate the values generated by an expression returning the a “Total Amount Billed,” consisting of the addition of all the values in three columns. To do so, we'll take the following steps from the Internet Sales tab:

17. Click the top of the column labeled “CarrierTrackingNumber,” which appears three columns to the right of the Total Internet Sales column that we added, in earlier steps, to the Internet Sales tab, as shown.

Illustration 26: Click the Top of the Column Labeled “CarrierTrackingNumber”

18. Right-click the column label for the CarrierTrackingNumber column.
19. Select Insert Column from the context menu that appears, as before.

Illustration 27: Click Insert Column on the Context Menu, as Before…

A new calculated column, labelled CalculatedColumn1, appears, as we noted in earlier steps.

20. Select the function button (“fx”) to the left of the formula bar next, as we did earlier.

The Insert Function dialog appears, once more.

21. Click the downward pointing arrow, once again, on the selector appearing atop the dialog under the Select a category label.
22. Select the Math & Trig category.
23. In the Select a function list that populates for the Math & Trig category, select the SUMX() function, as shown in Illustration 28.

Illustration 28: Select the SUMX() Function

24. Click OK to confirm the selection and dismiss the Insert Function dialog, as we did earlier.

The SUMX keyword, proceeded by the “=” operator and followed by a left parenthesis ( “(“ ), appears in the formula bar, with a function usage tooltip appearing just underneath the function, as we noted in working with the SUM() function earlier.

Illustration 29: The Beginning of Another Function, this time SUMX(), along with Tooltip…

We will continue with the creation of another simple calculation (although, by the very nature of the SUMX() function, slightly more sophisticated than the SUM() function with which we worked earlier); this time, we will generate a “Total Amount Billed” that generates a sum of the values of the SalesAmount, TaxAmt and Freight columns of the Internet Sales tab.

25. Following the left parenthesis inserted by our selection of SUMX, begin to type in the word “InternetSales.”
26. Scroll down and select the ‘Internet Sales' table within the dropdown list to double-click / select the column, as depicted in Illustration 30.

Illustration 30: Select the ‘Internet Sales' Table…

27. Once the selector is dismissed, and =SUMX('Internet Sales' appears in the formula bar, type a comma ( “ , “ ) after 'Internet Sales'.

The syntax, at this point, should be as shown here:

  =SUMX('Internet Sales',

28. After the comma that we have added at the end of the expression, type a left bracket ( [ ), and then begin to type in the word “SalesAmount”.
29. Scroll down and select the [SalesAmount] column within the dropdown list to double-click / select the column, as shown in Illustration 31.

Illustration 31: Select the [SalesAmount] Column…

30. Once the selector is dismissed, and =SUMX('Internet Sales',[SalesAmount] appears, type a plus sign ( “ + “ ) after [SalesAmount].
31. After the “ + ” that we have added at the end of the expression, type another left bracket (“ [ “), and then begin to type in the word “TaxAmt”.
32. Scroll down and select the [TaxAmt] column within the dropdown list to double-click / select the column, as depicted.

Illustration 32: Select the [TaxAmt] Column…

33. Once the selector is dismissed, and =SUMX('Internet Sales',[SalesAmount]+[TaxAmt] appears, type another plus sign ( “ + “) after [TaxAmt].
34. After the “+” that we have added at the end of the expression, type a left bracket ( [ ), and then begin to type in the word “Freight”.
35. Scroll down and select the [Freight] column within the dropdown list to double-click / select the column, as shown in Illustration 33.

Illustration 33: Select the [Freight] Column…

36. Once the selector is dismissed, and =SUMX('Internet Sales',[SalesAmount]+[TaxAmt]+[Freight] appears, we have only to type a right parenthesis ( “ ) “ ) after [Freight] to close`the SUMX() function.

The completed function appears as depicted in Illustration 34.

Illustration 34: The Completed SUMX() Function

37. Once the syntax shown above appears in the formula bar, press the ENTER key.

The new calculated column is populated, in every row, by the total of all values in the sums of the Sales Amount, Tax Amount, and Freight columns of the Internet Sales tab. Let's name the column to distinguish it, once again, at this stage.

38. Right-click the new column header label (currently at its default of “CalculatedColumn1”), and select Rename Column from the cascading menu that appears.

Illustration 35: Renaming the Column Label to Something More Meaningful

39. Name the newly-added column “Total Billed Amount” to provide a unique column header name.

The newly-named column, with updated formula results, appears as partially presented below.

Illustration 36: The Results Appear in Each Row of the New Column (Partial View)

It's easy to see how we can employ SUMX(), in this simple scenario, to deliver a “total of totals:” the total of three columns on each row, subsequently totaled for all rows within the calculated column itself, inside the specified Internet Sales table of the PowerPivot window.

Let's look at one more example of the use of SUMX(), via another calculated column in the Data Sheet View of PowerPivot. This time, we'll exploit a slightly more sophisticated use of the second argument within the SUMX() function. For purposes of this example, let's say that we have been tasked with meeting a requirement to return a total value for a column within a given table, filtered for a given date only, via the DAX FILTER() function. To be specific, let's say that the requirement is to return the total of the Discount Amount applied on Order Date August 1, 2008, within the Reseller Sales table of the existing PowerPivot model. To achieve this, we will take the following steps:

40. From within the Data Sheet View, click the Reseller Sales tab.
41. Click the top of the column labeled “ProductStandardCost” (the column to the immediate right of the DiscountAmount column) on the Reseller Sales tab, as shown.

Illustration 37: Click the Top of the Column Labeled “ProductStandardCost”

42. Right-click the column label for the ProductStandardCost column.
43. Select Insert Column from the context menu that appears, as before.

Illustration 38: Click Insert Column on the Context Menu, Once Again…

A new calculated column, labelled CalculatedColumn1, appears, as we noted in earlier steps.

44. Select the function button (“fx”) to the left of the formula bar next, as we did earlier.

The Insert Function dialog appears, once more.

45. Click the downward pointing arrow, once again, on the selector appearing atop the dialog under the Select a category label.
46. Select the Math & Trig category.
47. In the Select a function list that populates for the Math & Trig category, select the SUMX() function, as we did in earlier examples.
48. Click OK to confirm the selection and dismiss the Insert Function dialog, once again.

The SUMX keyword, again proceeded by the “=” operator and followed by a left parenthesis ( “(“ ), appears in the formula bar for the newly inserted calculated column.

Illustration 39: The Beginning of Another SUMX() Function, along with Tooltip…

We will continue with the creation of another calculation – a calculation that performs the summation of the rows of a column, with the rows filtered for criteria that exists within another column, as we will specify in the second argument of the function.

49. Following the left parenthesis inserted by our selection of SUMX, begin to type in the word “FILTER.”
50. Scroll down and select the FILTER function within the dropdown list to double-click / select the function, as depicted in Illustration 40.

Illustration 40: Select the FILTER Function…

51. Once the selector is dismissed, and =SUMX(FILTER(appears, begin to type in the words “Reseller Sales.”
52. Scroll down and select the ‘Reseller Sales' table within the dropdown list to double-click / select the column, as shown.

Illustration 41: Select the ‘Reseller Sales' Table…

53. Once the selector is dismissed, and =SUMX(FILTER('Reseller Sales' appears in the formula bar, type a comma ( , ) after 'Reseller Sales'.

The syntax in the function bar, at this point, should be as shown here:

  =SUMX(FILTER('Reseller Sales',

54. After the comma that we have added at the end of the expression, begin to type in the words “Reseller Sales,” once again.
55. Scroll down and select the ‘Reseller Sales'[OrderDateKey] column within the dropdown list to double-click / select the column, as depicted.

Illustration 42: Select the 'Reseller Sales'[OrderDateKey] Column…

56. Once the selector is dismissed, and =SUMX(FILTER('Reseller Sales', 'Reseller Sales'[OrderDateKey] appears, type an equals sign ( = ) after 'Reseller Sales'[OrderDateKey].
57. After the “=” that we have added at the end of the expression, type the integer 20050801.
58. Follow the integer 20050801with a right parenthesis ( “)” ), and then a comma ( , ).

The syntax in the function bar, at this point, should be as shown here:

=SUMX(FILTER('Reseller Sales', 'Reseller Sales'[OrderDateKey]=20050801),

59. After the comma that we have added at the end of the expression, type in a left bracket ( “ [ “).
60. After the left bracket just added, begin to type in the phrase “DiscountAmount.”
61. Scroll down and select the [DiscountAmount] column within the dropdown list to double-click / select the column, as shown.

Illustration 43: [DiscountAmount] Column…

62. Once the selector is dismissed, and =SUMX(FILTER('Reseller Sales', 'Reseller Sales'[OrderDateKey]=20050801),[DiscountAmount] appears, type a right parenthesis ( “ ) “ ) after [DiscountAmount].

The completed function should be as shown here:

 =SUMX(FILTER('Reseller Sales', 'Reseller Sales'[OrderDateKey]=20050801),[DiscountAmount])

… and appears as presented in Illustration 44.

Illustration 44: The Completed SUMX() Function

63. Once the syntax shown above appears in the formula bar, press the ENTER key.

The expression we have assembled first filters the table, Reseller Sales, on the expression, OrderDateKey = 20050801, and then returns the sum of all values in the column, DiscountAmount. In other words, the expression returns the sum of discounts for only the order date of August 1, 2008. Let’s name the column to distinguish it, as we have done with calculated columns we have created before.

64. Right-click the new column header label (currently at its default of “CalculatedColumn1,” once again), and select Rename Column from the cascading menu that appears, as we have done in earlier examples.
65. Name the newly added column “Total Sales Discounts 08-01-2005” to provide a unique column header name.

The newly-named column, with updated formula results, appears as partially shown.

Illustration 45: The Results Again Appear in Each Row of the New Column (Partial View)

Let’s check our result with a simple test:

66. On the Reseller Sales tab in the Data Sheet View, scroll over to the OrderDateKey column.
67. Find a row in the OrderDateKey column where the value is 20050801, and then right-click on the cell containing that value.
68. Select Filter --- > Filter by Selected Cell Value from the cascading context menus that appear, as shown.

Illustration 46: Filter by Selected Cell Value…

The column is filtered to display only row items with 20050801 as the OrderDateKey.

69. Click on the column label for the newly filtered DiscountAmount column, selecting the column.
70. Right-click the column heading.
71. Select Copy from the context menu that appears, as depicted.

Illustration 47: Copy the Filtered DiscountAmount Column…

72. Click the Switch to Workbook button in the toolbar in the upper left corner of the PowerPivot Window, as shown, to return to the Excel workbook.

Illustration 48: Transit to the Excel Workbook

73. Once the worksheet appears, right-click the cell in the upper left corner of the spreadsheet.
74. Select the first of the two buttons appearing under Paste Options, on the context menu that appears next, as depicted.

Illustration 49: Select the Left-most Paste Button to Paste…

The filtered DiscountAmount column appears on the spreadsheet.

75. Right-click the cell underneath the bottom cell of the new column, to select it.
76. Click the Sum (Sigma, or “ S “) button on the Home toolbar to sum the values above the chosen cell, as shown.

Illustration 50: Summing the Filtered Column…

We compare the total of the filtered SalesDiscount column in the spreadsheet to the total generated by our most recent SUMX() function in each row of the SalesDiscount column on the Reseller Sales tab in the PowerPivot window and see that the totals agree (with rounding differences).

Illustration 51: Proofing SUMX() Results in the Calculated Column in PowerPivot (Composite View)

So our “quick and dirty” test shows that the SUMX() function has performed as expected.

In the section that follows, we will access the PowerPivot calculated columns we have created within a PivotTable. Moreover, we will get some hands-on exposure to putting the DAX SUM() and SUMX() functions to work within a calculated measure we will create in the PivotTable.

PivotTable Measure

Keeping in mind the assertion we make throughout the Stairway to PowerPivot and DAX series, that PowerPivot exists, in its fundamental role, to act as a data source for PivotTables, we will now work further with the DAX functions we have exposed in the PowerPivot calculated column section above. The difference, this time, will be that we will present values and calculations within a PivotTable.

As most of us know by now, we can begin our work with a PivotTable from either the Excel workbook or from the PowerPivot window. Because we’re already in the latter, we will proceed by taking the following steps:

1. From the Reseller Sales tab in the PowerPivot window, click the downward pointing arrow on the PivotTable button to cause the drop-down menu to appear, as shown.

Illustration 52: The PowerPivot PivotTable Drop-down Menu Appears…

While we will work with examples, at various times throughout our series, of some of the other options we see in the dropdown menu, we will build a couple of standalone PivotTables in this session, as this will be the most efficient path to getting hands-on exposure to the underlying PowerPivot data and DAX, in general.

2. Click PivotTable in the dropdown menu we’ve exposed (the top selection).

We are returned to the Excel worksheet, where we encounter the Create PivotTable dialog.

3. Ensure that the radio button labeled New Worksheet is selected.

Illustration 53: Ensure the New Worksheet Option is Selected…

4. Click OK to accept the setting and dismiss the dialog.

The PivotTable placeholder / canvas, together with the PowerPivot Field List, appear on the new worksheet as depicted (compressed view):

Illustration 54: The PivotTable Canvas Appears on the New Worksheet (Compressed View)

We’ll leave the placement of the new PivotTable at default.

To gain some exposure to the use of the DAX SUM() and SUMX() functions within the PivotTable, we will replicate some of the steps we took with regard to the Internet Sales and Reseller Sales tables, upon which we operated in the PowerPivot window in the earlier part of this Level. In accomplishing this, we’ll see how to approach what we did with the calculated column earlier, but this time we’ll do so with a calculated measure in the PivotTable. Moreover, we will touch upon more PivotTable – specific considerations as we encounter them. But first, we will pull calculations we created earlier into a new PivotTable we construct, to allow us to see the output of our calculation when we select it from the PowerPivot Field List.

5. Collapse the Customer table (expanded by default) in the Field List by clicking the “-” sign to its immediate left.
6. Expand both of the Product Category and Product Subcategory tables.
7. Select the EnglishProductCategoryName and EnglishProductSubcategoryName from the Product Category and Product Subcategory tables, respectively, by placing a check in the associated checkboxes, as shown.

Illustration 55: Row Axis: Selections from the Product Category and Product Subcategory Tables

Our initial rows axis is in place at this stage. (The two selections we made were placed into the Row Labels drop zone of the PowerPivot Field List by default, as they are non-numeric values.) Now let’s add some values: calculated columns with which we worked in the PowerPivot window in our earlier sections.

8. Expand the Internet Sales table in the Field List by clicking the “+” sign to its immediate left.
9. Select the Total Internet Sales and Total Billed Amount from the Internet Sales table, in that order, by placing a check in the associated checkboxes, as shown.

Illustration 56: Value Selections from Calculated Columns in the Internet Sales Table

Recall that the selections we have made from the Internet Sales table represent the calculated columns we created in the Internet Sales table of the PowerPivot Window. The first, Total Internet Sales, relies upon the DAX SUM() function, the syntax of which is as follows:

SUM('Internet Sales'[SalesAmount]

The second calculated column, Total Billed Amount, exploits the DAX SUMX() function, using the following syntax:

=SUMX('Internet Sales', [SalesAmount]+[TaxAmt]+[Freight])

The PivotTable summary we have constructed to this point appears as shown:

Illustration 57: PivotTable Summary with Calculated Columns Data from Internet Sales Table

We note that the values presented in the columns, which we have populated by dragging the respective calculated columns into the PivotTable, seem quite large. To research why we have a difference, not to mention to ascertain which values are correct, let’s delve into the nature of our values a bit. First, let’s take a look at recreating the Sum of Total Billed calculation, currently a calculated column from the Internet Sales tab in the underlying PowerPivot Window that is pulled into the PivotTable, much as if it were any other column in the Internet Sales tab (there isn’t even any way to distinguish a calculated column from a standard column within the PowerPivot Field List for the Pivot Table under examination). We can replicate this calculation locally in the PivotTable by taking the following steps.

10. Return to the PowerPivot tab, if necessary, from the worksheet containing our current PivotTables.
11. Click the New Measure button in the ribbon atop the tab (to the right of the PowerPivot Window button in the upper left of the ribbon), as presented here:

Illustration 58: Click the New Measure Button…

The Measure Settings dialog appears.

12. Select Internet Sales in the dropdown selector labeled Table name atop the dialog.
13. Type (or copy and paste) the following into both the Measure Name and Custom Name boxes of the dialog.

Total Billed Amount CM

(We are simply naming the calculated measure in a way to distinguish it from the entrained calculate column already in the PivotTable.)

14. Click the function button (“fx”) to the right of the Formula label, above the Formula input box of the dialog.

The Insert Function dialog appears.

15. Leave the Select a category label at the default setting of “All” this time.
16. Scroll, within the Select a function dropdown, to the SUMX() function.
17. Select the SUMX() function, as depicted:

Illustration 59: Creating the New “Total Billed Amount CM” Measure…

18. Click OK to confirm the selection and dismiss the Insert Function dialog.

The SUMX() keyword, proceeded by the “=” operator, and followed by a left parenthesis, appears in the Formula input box of the Measure Settings dialog.

19. To the immediate right of the left parenthesis (that appears to the right of the SUMX keyword), begin to type in “Internet Sales.”

As we begin to type the table name, AutoComplete again presents us with a list of selections that begin with Internet Sales, similar to the behavior to which we have become accustomed within the PowerPivot Window.

20. Select ‘Internet Sales’ from the dropdown selector, as shown.

Illustration 60: Selecting the Desired Table…

21. Add a comma ( “ , “ ), and then a left bracket ( “ [ “) as shown below, after ‘Internet Sales'.

The syntax in the function input box should appear like this, at this point:

=SUMX('Internet Sales',[

22. Begin the type in the expression “Sales Amount,” (no punctuation needed).
23. Select [SalesAmount] from the dropdown selector, as shown.

Illustration 61: Selecting the [SalesAmount] Column…

24. Add a plus sign ( “ + “ ), and then another left bracket ( “ [ “) as shown below, after [SalesAmount].

The syntax in the function input box should appear like this, at this point:

=SUMX('Internet Sales', [SalesAmount]+[

25. Begin the type in the expression “TaxAmt” (no punctuation needed).
26. Select [TaxAmt] from the dropdown selector that appears.
27. Add a plus sign ( “ + “ ), and then another left bracket ( “ [ “) as shown below, after [TaxAmt].

The syntax in the function input box should appear like this, at this point:

=SUMX('Internet Sales', [SalesAmount]+[TaxAmt]+[

28. Finally, begin to type in the expression “Freight” (again, no punctuation needed).
29. Select [Freight] from the dropdown selector that appears.
30. Add a right parenthesis ( “ ) “ ), to close the SUMX() function.

The syntax in the function input box should appear like this, at this point:

=SUMX('Internet Sales', [SalesAmount]+[TaxAmt]+[Freight])

31. Select Currency in the Formatting Options section (the “Category” selections in bottom left corner) of the Measure Settings dialog, leaving the other formatting settings at default.

The Measure Settings dialog appears, with our input, as depicted.

Illustration 62: The Measure Settings Dialog, with Our Input

32. Click OK to accept our input and dismiss the Measure Settings dialog.

At this juncture, the new Total Billed Amount CM calculation appears, within its home PivotTable, as shown:

Illustration 63: The New Total Billed Amount CM Calculation Appears…

In this single example, we illustrate the importance of “choosing the right calculation” (that is, choosing between a calculated column in PowerPivot and a calculated measure in the PivotTable) for presentation in a PivotTable: While the new Total Billed Amount CM calculated measure, as well as the Sum of Total Billed Amount value that appears to its immediate left, share much the same logic, the huge difference in their presented values lies in the fact that the latter, a calculated column, is a “sum of sums.” The calculation is summed a second time when we add it to the Values drop zone of the PivotTable. The total at bottom of the column ($1,959,392,117,335.84) equals the total of the calculated measure, $32,441,341.06, added to itself as many times as there are rows within the calculated column within the underlying PowerPivot tab (Internet Sales). We can prove this to be the case by simply copying and pasting the entire Total Billed Amount calculated column to a blank column in a spreadsheet, and totaling that column at bottom, as we did in another exercise earlier – only a minor rounding difference appears between the test total and the Sum of Total Billed Amount total.

We have noted in other Levels of this series that there are multiple things to consider in choosing “which total to use” in a given PivotTable, in similar cases. In this example, we can see that the “sum of a sum” is hardly likely to deliver the intended outcome.

Let’s next examine a new calculated measure that we will create within the PivotTable to broaden our exposure to a slightly more sophisticated use.

33. Click the first data row in the right-most column, Total Billed Amount CM, of the PivotTable within which we have just been working.
34. Once the row in the Total Billed Amount CM column of the PivotTable is selected, right click the Internet Sales table within the PowerPivot Field List for the current PivotTable.
35. Select Add New Measure… from the context menu that appears, as we have done before.

The Measure Settings dialog appears.

36. Ensure that Internet Sales appears within the dropdown selector labeled Table name atop the dialog.
37. Type (or copy and paste) the following into both the Measure Name and Custom Name boxes of the dialog.

  SalesAmount_FinishedGoods

38. Click the function button (“fx”) to the right of the Formula label, above the Formula input box of the dialog, once again.

The Insert Function dialog appears.

39. Leave the Select a category label at the default setting of “All,” once again.
40. Scroll, within the Select a function dropdown, to the SUMX() function.
41. Select the SUMX() function, as we did in our last example.
42. Click OK to confirm the selection and dismiss the Insert Function dialog.

The SUMX() keyword, proceeded by the “=” operator, and followed by a left parenthesis, appears in the Formula input box of the Measure Settings dialog, once again.

43. To the immediate right of the left parenthesis (that appears to the right of the SUMX keyword), type an “F.”
44. Double-click to select the FILTER() function from the AutoComplete list that appears, as shown.

Illustration 63: Select FILTER() from the AutoComplete List that Appears…

The FILTER keyword, with a left parenthesis (“ ( “ ) to its immediate right, appears in the Formula input box, so that the input box content appears like this:

  =SUMX(FILTER(

45. To the immediate right of the left parenthesis (that appears to the right of the FILTER keyword), begin typing “Internet Sales.”
46. Select ‘Internet Sales' from the dropdown selector, as shown.

Illustration 64: Selecting the Desired Table, Once Again…

47. Add a comma ( “ , “ ) after ‘Internet Sales'.

The syntax in the function input box should appear like this, at this point:

  =SUMX(FILTER('Internet Sales',

48. To the immediate right of the comma (that appears to the right of ‘Internet Sales'), type an “R.”
49. Select the DAX RELATED() function from the dropdown selector that appears, as depicted, by double-clicking it.

Illustration 65: Selecting the DAX RELATED() Function into the Formula…

The RELATED keyword appears, complete with a left parenthesis ( “ ( “ ) to its immediate left, in the Formula input box.

50. To the immediate right of the left bracket ( “ ( “ ), which appears to the right of the RELATED keyword, begin typing in the word “Product.”
51. Scroll down, and select the Product[FinishedGoodsFlag] column from the dropdown selector, as shown.

Illustration 66: Selecting the Product[FinishedGoodsFlag] Column…

52. Add a right parenthesis ( “ ) “ ) after [FinishedGoodsFlag] to complete the measure formula.

The syntax in the function input box should now appear like this, at the present stage:

  =SUMX(FILTER('Internet Sales',RELATED('Product'[FinishedGoodsFlag])

53. Type an “equals” sign ( “ = “ ) next, followed by a “TRUE”, (without quotation marks) like this:

  =TRUE

54. Follow “TRUE” with a closing right parenthesis ( “ ) “), followed by a comma ( “ , “ ).
55. Type a left bracket ( “ [ “ ), and then begin typing in the term “SalesAmount.”
56. Select [SalesAmount] from the dropdown selector, as depicted.

Illustration 67: Selecting the [SalesAmount] Column…

57. Finally, add a right parenthesis ( “ ) “ ) after [SalesAmount] to complete the measure formula.

The complete syntax in the function input box should appear like this:

  =SUMX(FILTER('Internet Sales',RELATED(Product[FinishedGoodsFlag])=TRUE),[SalesAmount])

58. Perform a quick syntax check by clicking the Check formula button above the Formula input box.

A message should appear indicating no errors.

59. Select Currency in the Formatting Options section (again, the “Category” selections in bottom left corner) of the Measure Settings dialog, leaving the other formatting settings at default.

The Measure Settings dialog appears, with our input, as shown.

Illustration 68: The Measure Settings Dialog, with Our Input

Click OK to accept our input and dismiss the Measure Settings dialog.

At this juncture, the new SalesAmount_FinishedGoods calculation appears within the PivotTable as shown:

Illustration 69: The New SalesAmount_FinishedGoods Calculation Appears…

This exercise has demonstrated, once again, that we can use DAX functions together to achieve various behaviors we need to generate to meet business needs. And, as a part of learning about the DAX SUMX() function for the first time in our series, we have revisited the use of a couple of DAX functions that we introduced in earlier Levels of the series. We explored the RELATED() function in detail in Stairway to PowerPivot and DAX - Level 1: Getting Started with PowerPivot and DAX. Moreover, we got some exposure to the FILTER() function in Level 2: The DAX COUNTROWS() and FILTER() Functions and More About Calculated Columns and Measures

We can see specifically, once again, how we can use SUMX() to achieve a refined aggregation. To review the formula we just constructed:

  =SUMX(FILTER('Internet Sales',RELATED(Product[FinishedGoodsFlag])=TRUE),[SalesAmount])

…we can wind our way through the construction as follows:

Included within the first argument of the SUMX() function are the FILTER() and RELATED() functions. The FILTER() function, in turn, takes two arguments. The first argument is the source table (Internet Sales), and the second is the filter condition by which we are limiting the SUMX() function. In our example, the condition specifies that the calculated measure should aggregate data only for those products in the Product table whose Finished Goods flag is set to “TRUE.” We use the RELATED() function to reference the related / joined Product table.

This example illustrates only a simple instance of the sorts of aggregations we can accomplish with DAX. (We explore others, beyond simple sums, in other Levels of this series.) We can analyze and report upon data based upon date ranges, among other criteria, and create conditions (from simple to complex) that further refine the data, and / or include data taken from multiple tables. Performing calculations with these objectives before the advent of PowerPivot and DAX would likely have required a knowledge of relational and online analytical processing data structures, among other things. DAX empowers us to generate and support comprehensive BI within our Excel environments, transforming the relatively limited options we once had with simple spreadsheets into sophisticated and robust reporting and analysis solutions.

Summary

In this Level of the Stairway to PowerPivot and DAX series, we exposed the DAX SUM() and SUMX() functions. We discussed the general purposes and operation of each of these two DAX “staples,” and then focused upon using each in general, as well as comparing and contrasting ways that each can be employed, particularly in situations where we can combine them with other functions to achieve results like those we might need to generate in client or employer environments.

In like manner to our standard approach to every DAX function we explore within the Stairway to PowerPivot and DAX series, we examined the syntax surrounding each of the SUM() and SUMX() functions, undertaking illustrative examples of the uses of each in practice exercises, and then exploring the results datasets we obtained in the practice examples. We combined each of SUM() and SUMX() with other functions to deliver illustrative answers to business questions, within both PowerPivot calculated columns and PivotTable calculated measures we built in the section that followed. As we put calculations of both stripes to use within the PivotTable, we compared and contrasted the values returned by each, explaining the reasons behind differences in the datasets they returned, and discussing considerations in choosing which of the two calculation approaches to take in meeting business requirements in the business world.