Data View and Diagram View

Up until now, we have worked exclusively in data view; that is, we have looked at tables and the detail of the data. When moving to the design phase of your data preparation in PowerPivot, it is often easier to switch to diagram view, as this allows you to step back from the detail and look at the data set as a whole. To do this:

1. Click on the Design View button in the Home ribbon. PowerPivot will display the tables in diagram view, as shown in Figure 10-1.

As you can see from Figure 10-1, you are now looking at most or all of your tables, and although you can see the table and column names, you cannot see the data. This is exactly what we need, because now it is time to think in terms of overall structures rather than the nitty gritty. You can use this view to move and resize the tables. Moving a table is as easy as dragging the table’s title bar. Resizing a table means placing the pointer over a table edge or corner and dragging the mouse.

Although repositioning tables can be considered pure aesthetics, I find that doing so is really useful. A well laid out data set design will help you understand the relationships between the tables and the inherent structure of the data.

Creating Relationships

Creating relationships is easy once you know which fields are common between tables. Since we already agreed that we need to join the Colours table to the SalesData table, let’s see how to do this.

1. Drag the ColourID field from the SalesData table over the ColourID field in the Colours table.

A thin arrow will join the two tables as shown in Figure 10-3.

Creating Relationships Manually

You do not have to drag and drop field names to create relationships. If you prefer, you can specify the tables and fields that will be used to create a relationship between tables. What is more, you do not have to be in diagram view to do this. So, just to make a point and to show you how flexible PowerPivot can be, in this example, you will join the Countries and Clients tables on their common CountryID field:

1. Select the Countries table.
2. Click the Create Relationship button in the Design ribbon. The Create Relationship dialog will appear.
3. On the upper row, where the Countries table is already selected (because you started from the Countries table), select the CountryID field.
4. On the lower row, select the Clients table as the Related Lookup Table. The CountryID field should appear automatically as the field to join on (in the Related Lookup Column field). If PowerPivot has guessed the field, it will display a tiny “i” icon on a round background. If it does not, or if it has guessed incorrectly, you can always select the correct field from the Related Lookup Column popup. The Create Relationship dialog should look like Figure 10-4.

   

   Figure 10-4. The Create Relationship dialog

5. Click Create. The relationship will be created.

Creating Relationships Automatically

If you are importing several tables from a relational database, then you can have PowerPivot to create the relationships during the import process. This approach has a couple of advantages:

• You avoid a lot of manual work.
• You reduce the risk of error (that is, creating relationships between tables on the wrong fields, or even creating relationships between tables that are not related).

This technique is unbelievably easy. You carry out an import from a relational database source (say, using SQL Server). When faced with the list of available tables in the source database (as described in Chapter 9), you

1. Select the major source table.
2. Click the Select Related Tables button. Any tables that are linked to the table you have selected will be selected.
3. Continue the import process as described previously.

Once you have completed the import, switch to diagram view. You will see that the tables you just imported already have the relationships generated in PowerPivot.

Deleting Relationships

In addition to creating relationships, you will inevitably want to remove them at some point. This is both visual and intuitive.

1. Click on the Design View button in the Home ribbon. PowerPivot will display the tables in diagram view.
2. Select the relationship that you want to delete. The arrow joining the two tables will become a double link, and the two tables will be highlighted.
3. Right-click and choose Delete (or press the Delete key). The Confirmation dialog will appear.
4. Click Delete From Model.

The relationship will be deleted and the tables will remain in the data model.

Managing Relationships

If you wish to change the field in a table that serves as the basis for a relationship, then you have another option. You can use the Manage Relationships dialog. This approach may also be useful if you want to create or delete several relationships at once. If you want to use this

1. In the Design tab, click Manage Relationships. The Manage Relationships dialog appears, as shown in Figure 10-5.

   

   Figure 10-5. The Manage Relationships dialog

2. Click the relationship you wish to modify.
3. Click Edit. The Edit Relationship dialog appears (it is virtually identical to the Create Relationship dialog shown in Figure 10-4).
4. Continue modifying the relationship as described previously.

As you can see from this dialog, you also have the option of creating or deleting relationships. Since the processes here are identical to those I have already described, I will not repeat them. If you want to practice using the Edit Relationship dialog, you can use it to complete the data model by adding the following final relationships:

• The SalesData to Clients table on the ClientID field in each table.
• The DateTable table to the SalesData table on the DateKey and InvoiceDate fields, respectively.

The data model will then look like the one given in Chapter 1, Figure 1-13.

The techniques used to create and manage relationships are not, in themselves, very difficult to apply. It is nonetheless absolutely fundamental to establish the correct relationships between the tables in the data set. Put simply, if you try and use data from unconnected tables in a single Power View visualization or Excel pivot table, you will not just get an alert warning you that relationships need to be created, you will also get visibly inaccurate results. Basically, all your analysis will be false. So it is well worth it to spend a few minutes upfront designing a clean, accurate, and logically coherent data set.

Marking a Table as a Date Table

PowerPivot now needs to know that the table you have imported is, in fact, a date table that it can use to add time intelligence. This is easier to do than to talk about:

1. Click on the DateTable tab (in data view) or the table itself (in display view).
2. In the Design tab, click the Mark As Date Table button.
3. Switch to the data view.
4. Click on the tab for the DateTable.
5. Select the column that contains the contiguous list of dates. In the example used in this book, it is the DateKey column.
6. To make sure everything has gone well, click on the lower part of the Mark As Date Table button (the small downward-facing triangle) and select Date Table Settings from the popup menu. The Mark As Date Table dialog will appear.
7. The selected (date key) column should appear in the Date popup. If this is not the case, select it from the popup.
8. Click OK.

Now PowerPivot knows that this table is slightly special, and that it contains only a list of dates that can be used to add time intelligence to your analyses. The final thing to do is define a relationship between the DateKey column in the date table and a date field in the table that contains the data you want to analyze over time. In the sample data that we are using, this will be the InvoiceDate field in the SalesDate table. This way the data set knows that you may be looking at sales by invoice date, but that the DateTable will provide the list of days, months, quarters, and years used to display metrics over time.

Simple Calculations

To show you what can be done, I will presume that, when working with Power View, you have met a need for a single column of data that contains both the make and model of every car sold. Because the data we imported contains this information as separate columns, we need to add a new column that takes the data from the columns Make and Marque, and joins them together (or concatenates them, if you prefer) in a new column. Here is how it can be done:

1. In the PowerPivot window, make sure that you are in data view.
2. Click on the SalesData tab to select the SalesData table.
3. Scroll to the right of the data in the table and click anywhere in the blank column that appears after the final column of data. This column is currently entitled Add Column.
4. Enter the = (the equals sign). The equals sign will appear in the formula bar above the table of data.
5. Scroll left and click anywhere inside the Make column. The formula bar now reads =[Make].
6. In the formula bar add: &" "&. The formula bar now reads =[Make] & " " &.
7. Click anywhere inside the Marque column. The formula bar now reads =[Make] & " " & [Marque].
8. Press Enter. The cursor jumps back to your starting point in the new column at the right of the table. The column is filled automatically with the result of the formula and shows the make and model of each car sold.
9. Right-click on the column header for the new column and select Rename.
10. Enter the word Vehicle and press Enter.

The table will now look something like Figure 10-7.

I imagine that if you have been using Excel for any length of time, then you might have a strong sense of déjà vu after seeing this. After all, what you just did is virtually what you would have done in Excel. All you have to remember is that

• Any additional columns are added to the right of the existing columns. You can always move them elsewhere in the table once they have been created.
• All functions begin with the equals sign.
• Any function can be developed and edited in the formula bar at the top of the table.
• Reference is always made to columns, not to cells (as you would in Excel). Indeed, you can click anywhere inside a column to obtain a reference to a column, as you did in step 5 in the preceding exercise, or you can type the column name (in square brackets).
• Column names are always enclosed in square brackets.
• You can nest calculations in parentheses to force inner calculations before outer calculations—again, just as you would in Excel.

To extend the basic principle, and also to show a couple of variations on a theme, let’s now add a calculation to the SalesData table. More precisely, I will assume that our Power View visualizations will frequently need to display the figure for net sales, which I will define as being the sales figure minus any sales-related costs. To obtain this, by applying a variation on a technique that we used before,

1. Click in the blank column at the right of the SalesData table.
2. Click inside the formula bar.
3. Enter the equals sign.
4. Enter a left square bracket([). The list of the fields available in the SalesData table will appear in the formula bar, as shown in Figure 10-8.

   

   Figure 10-8. Selecting available columns from a table

5. Type the first few characters of the column that you want to reference—SalePrice, in this example. The more characters you type, the fewer columns will be displayed in the list.
6. Click on the column name. It will appear in the formula bar.
7. Enter the plus sign (+) or any other mathematical operator you want to use.
8. Continue repeating steps 5 and 6 until you have built the formula you require. In this example, it is

   =[SalePrice] - ([TotalDiscount] + [DeliveryCharge])

9. Click the check box in the formula bar (or press Enter) and the new column will be created. You can now rename and reposition the column.

Using arithmetic in calculated columns in PowerPivot is almost the same as using calculating cells in Excel. Consequently I will not re-explain things you most likely already know. Just use the same arithmetical operators as you would use in Excel and, after a little practice, you should be able to produce calculated columns with ease. If you want to practice a little, begin by adding the columns described in Table 10-3. Adding these columns will prepare the PowerPivot data model, which is used as the basis for all the Power View examples in the previous chapters. These columns are also necessary for some of the formulas that we will use later in this chapter.

Using Formulas in Calculated Columns

Over a few pages you have seen just how easy it is to extend a data model with some essential metrics that you can then use in Power View, or indeed any other end user tool that can take PowerPivot data as its source of information. Yet we have only performed simple arithmetic to achieve our ends. PowerPivot can, of course, do much more than just carry out simple sums. Indeed, it comes with an incredibly powerful data manipulation language called DAX (short for Data Analysis eXpressions).

I am afraid that explaining all the possibilities of DAX would take an entire book, so all I want to do for the rest of this chapter is explain how you can use some core DAX functions in a handful of useful calculations. To begin with—and as an admittedly extremely simple example—I will presume that we need to calculate the age of every car sold relative to the current date. As the source data contains the registration date for each vehicle, this will not be difficult. So, what you have to do is

1. With the SalesData table selected, activate the Design ribbon and click inside any column (not necessarily the empty column at the right of the table).
2. Click Insert Function. The Insert Function dialog will display.
3. From the Select A Category popup at the top of the dialog choose Date And Time.
4. In the Select A Function list click on NOW. The dialog will look like Figure 10-9.

   

   Figure 10-9. The Insert Function dialog

5. Click OK. The function is added to the new column and appears in the formula bar. The cursor also jumps directly to the new column at the right of the existing data.
6. Extend the formula so it reads as follows. Remember that you can either enter the Registration_Date field or select it, as described earlier.

   =(NOW()-[Registration_Date])/365

7. Press Enter or click the check box in the formula bar. The formula will be added to the entire new column, and you can rename it (I will call it CarAge), move it, and format it as you wish.

Let me be clear, this is not the only way to calculate a time difference using DAX. It is probably not even the best one. It is, however, a simple yet comprehensible introduction to a DAX function which, once again, reminds you just how close a cousin DAX is to the Excel function language. It also shows you an easy way to see exactly what DAX functions are available and what they do, as each function displays a brief explanation when you click on it in the Insert Function dialog.

There is another way to insert functions if you prefer. If you are building a DAX formula in the formula bar, you can call up DAX formulas in the following ways:

• Click on the Fx icon in the formula bar (again, as you would in Excel). This brings up the Insert Function dialog.
• Start typing in the first few characters of the formula—assuming you know that it exists and how it is spelled. This will display a short list of all DAX functions that begin with the characters that you have typed. This is shown in Figure 10-10.

To give you another example of a slightly more complex DAX function, but one that is very necessary, consider the following requirement. Our data now has the car age, but we want to group the cars by age buckets. So we will use a nested IF function to do this. Then, to allow us to sort the column in a more coherent way, we will create a Sort By column for the new CarAgeBucket column that we created. I realize that I have not yet explained what a Sort By column is; its use will be explained in the next chapter.

As these columns use the same techniques that you have seen in the last few pages, I will not take you through them step by step but will show you the final, working code as it appears in the sample file. You can then re-create it if you want to as practice. For the sake of clarity, you need to know that the two formulas that follow are based on the following logical DAX functions:

• IF—requires three parameters (just as Excel does): a test, a true result, and a false result.
• AND—allows you to evaluate a number of comma-separated elements, all of which must be true as part of the test in the IF function. Just as in Excel you also have an OR function and a NOT function available.

The code for the CarAgeBucket column is as follows:

=IF(
    [CarAge] <=5,"Under 5",
    IF(AND([CarAge]>=6,[CarAge]<=10),"6-10",
       IF(AND([CarAge]>=11,[CarAge]<=15),"10-15",
          IF(AND([CarAge]>=16,[CarAge]<=20),"16-20",
             IF(AND([CarAge]>=21,[CarAge]<=25),"21-25",
                IF(AND([CarAge]>=26,[CarAge]<=30),"26-30",
                   ">30"
                  )
               )
            )
          )
       )
    )

The code for the CarAgeBucketSort column is

=IF([CarAge]<=5, "1",
    IF(AND([CarAge]>=6, [CarAge]<=10),"2",
       IF(AND([CarAge]>=11, [CarAge]<=15), "3",
          IF(AND([CarAge]>=16, [CarAge]<=20), "4" ,
             IF(AND([CarAge]>=21 , [CarAge]<=25), "5",
                IF(AND([CarAge]>=26, [CarAge]<=30),"6","7"
                  )
               )
            )
         )
      )
   )

These formulas could have come straight from an Excel spreadsheet. Indeed, some 80 of the DAX functions are nearly identical to their Excel cousins. So experience and imagination combined have shown me that you have many ways to extend the data you imported by adding calculated columns. Even better, all calculated columns are updated when you refresh the data from the source. The only major caveat is that you have to be careful if you are tweaking the data connection not to delete any source columns on which a calculated column depends, or you will get errors in the PowerPivot table.

Just in case you were wondering, you do not have to write formulas over multiple lines as I did here. Indeed, the two formulas used earlier could be written as follows:

=IF([CarAge] <=5,"Under 5",IF(AND([CarAge]>=6,[CarAge]<=10),"6-10",IF(AND([CarAge]>=11,[CarAge]<=15),"11-15",IF(AND([CarAge]>=16,[CarAge]<=20),"16-20",IF(AND([CarAge]>=21,[CarAge]<=25),"21-25",IF(AND([CarAge]>=26,[CarAge]<=30),"26-30","Over 30"))))))

And

=IF([CarAge] <=5,"1",IF(AND([CarAge]>=6,[CarAge]<=10),"2",IF(AND([CarAge]>=11,[CarAge]<=15),"3",IF(AND([CarAge]>=16,[CarAge]<=20),"4",IF(AND([CarAge]>=21,[CarAge]<=25),"5",IF(AND([CarAge]>=26,[CarAge]<=30),"6","7"))))))

I chose to write the formulas over multiple lines hoping that by doing so, I’d make the nested logic clearer. You can write your formulas in any way that suits you and that does not cause PowerPivot a problem.

Looking Up Related Data

Most likely, you will frequently want to look up data in another table and have it appear in the table that you are currently enhancing. If you are an Excel user, then you are probably trembling at the thought that there might be a VLOOKUP function in PowerPivot, too. Well, I have good news! The function is no longer as complicated or as lengthy to use to get the lookup in PowerPivot as it can be in Excel. To prove this, imagine that you are using the Clients table and you want to add a calculated column to display the client country that is currently in the Countries table. As the two tables are linked by a table relationship, all you have to do is

1. Scroll to the right of the Clients table and click in the blank column.
2. In the formula bar, add the following code:

   =RELATED(Countries[CountryName])

3. Confirm by pressing Enter. The country name for each client will appear in the new column.
4. Rename the column ClientGeography.

All that this function does is say, “Use the relationship to the Countries table, and bring back the CountryName field.” You do not need to specify how the tables are joined (in this case, on the CountryID fields) as that is defined by the relationship between the tables.

Multiline Formulas

All formulas that you create in PowerPivot will, by default, be on a single line. This can become an extremely wearing way of working, so it is worth knowing that you can tweak long formulas to force them to display over more than one line. All you have to do is force a line return inside the formula bar by pressing Shift-Enter where you want to force a new line. My experience is that PowerPivot will not let you create line breaks everywhere in a formula. Nonetheless, with a bit of trial and error, a more complicated formula, such as the CarAgeBucket column that you created previously, can look like it is in Figure 10-11.

A First Calculated Field: Number of Cars Sold

Suppose that you want to be able to display the number of cars sold in Power View. Not only that, but you want this figure to adjust when it is filtered or sliced by another criterion such as country or color. Put simply, you want this metric to be infinitely sensitive to how it is displayed, yet always give the right answer.

So how are we going to achieve this? Here is how:

1. Select the Table to which you wish to add a calculated field. I am choosing SalesData here.
2. Click in a blank cell in the calculation area under the data in the table.
3. Add the following formula to the formula bar:

   NumberOfCarsSold:=COUNTROWS(SalesData)

4. Confirm the creation of the formula by pressing Enter or by clicking the check mark icon in the formula bar.

The cell in the calculation area where you placed the calculated field should read NumberOfCarsSold:=154. That is, the formula has counted the sales records for the entire table. The name of the formula is NumberOfCarsSold. So, unlike calculated columns, you do not rename calculated fields; you include the name in the formula by entering the name followed by a colon. In this example, the field name did not contain spaces. Had this not been the case, it would have made no difference.

Not difficult, I am sure you will agree. Yet the best is yet to come. Suppose that you now use this field in a Power View table, which is also filtered to show the results for 2013 only, and has tiles. The result is shown in Figure 10-12.

The key thing to take away is that a correctly applied calculated field can be used in Power View or an Excel pivot table or chart and will always show the correct result of any and all filters and selections that you have applied. Also, in pivot tables, the figures will display the correct figure for each intersection of rows and columns. All in all, it is well worth ensuring that you have all the calculated fields that you need for your analytical output in place and that they are working correctly in PowerPivot, because you can then rely on these calculations in the data set in so many different visualizations.

Basic Aggregations in Calculated Fields

Calculated fields are DAX formulas, so in learning to use calculated fields you will have to become familiar with some more DAX than is generally required for calculated columns. My intention here is definitely not to take you through all that DAX can offer. However, I would like to show you a few basic formulas that can be practically useful in many cases, and as a result give you some initial DAX recipes that should prove of practical use.

As a second example, let’s calculate all sales revenue. Although you can just type in a simple DAX formula, I prefer to show you how you can extend the knowledge that you gained when creating calculated columns and apply many of the same techniques to creating calculated fields.

1. Select the SalesData table and click in a blank cell in the calculation area under the data in the table.
2. Enter the calculated field name (TotalSales) followed by a colon (:) and an equals sign (=).
3. Click the function icon in the formula bar. The Insert Function Dialog will appear.
4. Select Math & Trig as the category and SUM  as the function.
5. Click OK.
6. Start typing the table name (SalesData). After a couple of characters the table and all the fields that it contains will be listed, as shown in Figure 10-13.

   

   Figure 10-13. Creating a calculated field

7. Scroll down and select the SalesData[SalePrice]field.
8. Add a right parenthesis and press Enter (or click the check mark icon in the formula bar). The formula should read: TotalSales:=SUM(SalesData[SalePrice]).

The calculated field will be inserted into the calculation area. This particular function will give you the total of the SalePrice column. However, when you use it in Power View or a pivot table based on the PowerPivot data set, it will be filtered and applied (or sliced and diced if you prefer) to take into account how the data is subset.

One important thing to note when creating calculated fields is that you should almost always use the table name as well as the field name. Also, if a table name contains spaces, then the table name needs to be in single quotes. In all cases the field name has to be enclosed in square brackets.

To practice a little, try creating the average sale price using the following formula:

AverageSalePrice:=AVERAGE(SalesData[SalePrice])

This is still fairly close to an Excel formula, even if it is not identical. Anyway, now that you get the picture, you can use the following formulas (among others) to aggregate your data:

• SUM
• AVERAGE
• COUNT
• MAX
• MIN
• COUNTROWS

For a little practice, then, you could try adding the following calculated fields that are used in the chapters on Power View:

RatioCostToSales:=Sum(SalesData[CostPrice])/SUM(SalesData[SalePrice])

and

RatioNetMargin:=SUM(SalesData[NetMargin])/SUM(SalesData[SalePrice])

and finally

RatioGrossMarginToCosts:=SUM(SalesData[GrossMargin])/SUM(SalesData[CostPrice])

You may well find that some calculations are displayed (in the calculation area at least) to many decimal places. If you find this distracting, then you can format calculated fields in the same way that you format PowerPivot columns. Any formats that you apply will be used in Power View by default whenever you use this calculated field.

A final point is that when you insert a table or a field from the popup list that you can see in Figure 10-13, you will see three types of icon to the left of the table or field: the icon with a T denotes a PowerPivot table; the M icon indicates a calculated field; and the icon without a letter tells you that you are looking at a data field.

More Advanced Aggregations

Now that you have seen how to create basic calculated fields, it is time to move on to some more advanced concepts. More precisely, I want to outline a couple of ways to evaluate data on a row-by-row basis, yet return the result as a function of any filters and slicers. This cannot be done in many cases by using a calculated column and then returning the aggregate of the column data. Think, for instance, of calculating a ratio for each row and then adding up or averaging the results to get the average ratio; it will be arithmetically false.

PowerPivot, fortunately, has some simple yet powerful solutions to this kind of conundrum. One principal tool is the use of the “X” functions—AVERAGEX, COUNTX, SUMX, MAXX, and MINX among others. These functions allow you to specify

• The table in which the calculations will apply
• The row-by-row calculation that is to be applied

As an example, consider the requirement for the average gross margin per sale. Not only do we need this potentially at the finest level of granularity—the individual record—but we may need it sliced and diced by any number of criteria. So here is the formula that you could use in Power View reports:

AverageGrossMargin:=AVERAGEX(SalesData,[SalePrice]-[CostPrice])

Unlike the AVERAGE function, AVERAGEX takes two inputs (or parameters as they are technically known):

• The table to which the formula will be applied
• The formula to use, which is just as you would apply it to a calculated column

AverageIndirectCosts:=AVERAGEX(SalesData, [TotalDiscount] + [DeliveryCharge])

SalePriceAfterIndirectCostsRatio:=(SUM(SalesData[SalePrice]) - (SUM(SalesData[TotalDiscount]) + SUM(SalesData[DeliveryCharge]))) / SUM(SalesData[SalePrice])

Time-Dependent Calculations

The penultimate set of functions that I want to explain concern time, or rather, the dates used in analyzing data. PowerPivot calls this time intelligence (even though most of the time it refers to is the use of date ranges) and it can be a fundamental aspect of data presentation in business intelligence. After all, what enterprise does not need to know how this year’s figures compare to last year’s and what kind of progress is being made?

Time intelligence will always require a valid date table, which is one of the reasons why we spent a certain amount of time devoted to this earlier in the chapter. Then the date table has to be joined to the table containing the data that you want to compare over time on a date field. The good news is that once you have a valid date table, and have acquainted yourself with a handful of data and time functions in DAX, you can deliver some extremely impressive results. These kinds of calculations can cover

• YearToDate, QuarterToDate, and MonthToDate calculations
• Comparisons with previous years, quarters, or months
• Rolling aggregations over a period of time, such as the sum for the last three months
• Comparison with a parallel period in time, such as the same month in the previous year

As an introduction to time intelligence in PowerPivot, and also to give you a taste of some of the DAX functions that you are likely to use when analyzing data over time, let’s look at some of these calculations.

SalesQTD:=TOTALQTD(Sum(SalesData[SalePrice]), DateTable[DateKey])
 
SalesYTD:=TOTALYTD(Sum(SalesData[SalePrice]), DateTable[DateKey])

SalesPreviousYear:=CALCULATE(SUM(SalesData [SalePrice]), DATEADD(DateTable[DateKey], -1, YEAR))
 
AverageSalePricePreviousYear:=CALCULATE(
                                        AVERAGE(SalesData [SalePrice]),
                                        DATEADD(DateTable[DateKey], -1, YEAR)
                                       )

3MonthsToDate:=IF(
                  ISBLANK(SUM(SalesData[SalePrice])),
                          BLANK(),
                          CALCULATE(
                                    SUM(SalesData[SalePrice]),
                                    DATESINPERIOD(
                                                  DateTable[DateKey],
                                                  LASTDATE(DateTable[DateKey]),-3,MONTH
                                                 )
                                    )
                  )
 
Previous3Months:=IF(
                    ISBLANK(
                            CALCULATE(
                                      SUM(SalesData[SalePrice]),
                                      DATEADD(DateTable[DateKey],-1,MONTH)
                                     )
                           ),
                           BLANK(),
                           CALCULATE(
                                     SUM(SalesData[SalePrice]),
                                     DATESBETWEEN(
                                                DateTable[DateKey],
                                                FIRSTDATE(DATEADD(DateTable[DateKey],-4,MONTH)),
                                                LASTDATE(DATEADD(DateTable[DateKey],-1,MONTH))
                                     )
                           )
                     )

YearOnYearDelta:=IF(
                    ISBLANK(
                            SUM(SalesData [SalePrice])
                           ),
                    BLANK(),
                    IF(
                       ISBLANK(
                               CALCULATE(
                                         SUM(SalesData [SalePrice]),
                                         DATEADD(DateTable[DateKey], -1, YEAR)
                                        )
                              ),
                       BLANK(),
                       SUM(SalesData[SalePrice])
                       - CALCULATE(
                                   SUM(SalesData [SalePrice]),
                                   DATEADD(DateTable[DateKey], -1, YEAR)
                                  )
                       )
                     )
 
YearOnYearDeltaPercent:=IF(
                           ISBLANK(
                                   SUM(SalesData [SalePrice])
                                   ),
                           BLANK(),
                           IF(
                              ISBLANK(
                                      CALCULATE(
                                                SUM(SalesData [SalePrice]),
                                                DATEADD(DateTable[DateKey], -1, YEAR)
                                               )
                                     ),
                                      BLANK(),
                                      (
                                        SUM(SalesData[SalePrice])
                                        - CALCULATE(
                                                    SUM(SalesData [SalePrice]),
                                                    DATEADD(DateTable[DateKey], -1, YEAR)
                                                   )
                                      )
                                      /CALCULATE(
                                                 SUM(SalesData [SalePrice]),
                                                 DATEADD(DateTable[DateKey], -1, YEAR)
                                                )
                                      )
                               )

SUM(SalesData[SalePrice])
- CALCULATE(SUM(SalesData [SalePrice]), DATEADD(DateTable[DateKey], -1, YEAR))

SUM(SalesData[SalePrice]) - CALCULATE(SUM(SalesData [SalePrice]),DATEADD(DateTable[DateKey], -1, YEAR)) / CALCULATE(SUM(SalesData [SalePrice]),DATEADD(DateTable[DateKey], -1, YEAR))

Other Possibilities

As a final nod to the myriad possibilities that DAX offers, here is how to rank sales by the age of cars in the CarAgeBucket field:

SalesRankByAge:=RANKX(ALL(SalesData[CarAgeBucket]),SalesData[TotalSales],,,Dense)

Putting It All Together

Finally, if you have created all the calculated fields that are described in this chapter, the calculation area could look something like Figure 10-18.

A Few Comments and Notes on Using Calculated Fields

Calculated fields are an immense subject. The breadth and depth of the calculations that can be delivered using DAX are little short of astounding. Consequently, it is impossible in an introductory chapter to do anything other than give you a taste of what can be done and hopefully provide a few useful starter functions for you to adapt to your own requirements.

As you move on with DAX, a few things might help you on your way. The first concerns the use of calculated columns. Sometimes they are such an easy solution that it is a shame not to create them. However, they are stored in the table and do take up space. This means more space on disk and more space in memory. This is particularly true for a table containing tens of millions of rows. Calculated fields, on the other hand, are only calculated at run time, and so they take up virtually no space. So, if you are considering creating many calculated columns, perhaps some of them could become calculated fields instead.

Another trick worth knowing is that you can, of course, delete calculated fields by simply using the Delete key (or by right-clicking and choosing Delete). You will need to confirm this choice. However, you can also cut and paste calculated fields, one at a time, in the calculation area. This is a useful technique when you want to rearrange the fields into a more visibly coherent order.

Finally, and particularly if you are creating dozens of calculated fields, remember that you can increase the visible size of the calculation area. Simply drag the line separating the calculation area from the table data up or down.

Creating a Pivot Table

Assuming that your data set has all the requisite calculations in place, the time has come to create a simple pivot table. This can be a great way to test any calculations that you have developed and to see if they produce the results that you expect.

1. In the PowerPivot window (and you can be in either design view or data view), activate the Home ribbon.
2. Click the Pivot Table button. The Create Pivot Table dialog appears.
3. Select New Worksheet to create the pivot table in a new, independent worksheet. A new worksheet will be created and activated; that is, you will leave the PowerPivot window. This worksheet will contain an empty pivot table and will display the Field List containing the available fields from the PowerPivot data set, as shown in Figure 10-20.

   

   Figure 10-20. An empty pivot table using the PowerPivot data set

4. Expand the Countries table and drag the CountryName_EN field to the ROWS area.
5. Expand the Colours table and drag the Colours_EN field to the COLUMNS area.
6. Expand the SalesData table and drag the NumberOfCarsSold field to the VALUES area. The pivot table will look like Figure 10-21.

   

   Figure 10-21. A simple pivot table

This is, admittedly, the simplest pivot table I have ever made. However it serves to make the point that PowerPivot data sets can be the basis for pivot tables as well as Power View visualizations. Also, and at some risk of laboring the point, pivot tables are essential for testing your DAX formulas.

The PowerPivot Ribbon

Although we have not needed it so far, now is quite a good time to outline the buttons contained in the PowerPivot ribbon. These buttons are shown in Figure 10-22 and are then described in Table 10-5.

If you need to add calculated fields to the data model while working in a pivot table, you can do this without switching to the PowerPivot window. Simply click on the Calculated Fields button and define the field. Similarly, you can create KPIs (which you will see in the next chapter) by clicking the KPI button.