So far in various weeks we looked at how to create a basic PowerPivot table and even how to use DAX expressions to create additional columns. We even saw that calculated columns are a necessity when trying to join two table on two or more fields since PowerPivot will only allow join definitions between tables using a single column. The one area of DAX expressions we did not look at yet is creating new aggregate expressions. However, before we do that, we need to look at some simple no-code solutions to displaying calculated measures.
The example I’m using here is vaccination data for the state of Texas for the calendar years 2002 and 2003. I often use this SQL Server database as the data source for my PowerPivot presentations at SQL Saturday events because it is fairly straight forward requiring only a few related tables. In the following figure, I show a basic pivot table created from this database to display a count of office visits to get vaccinations by racial group and by year. (Click on image if it is fuzzy to see the image full size.)
Notice that the Visit ID values are huge to say the least, but that is because PowerPivot decided to sum the VisitID values since they were numeric values. Obviously, we do not want to sum this field. We only want to count the number of visits. We can easily change this default action by right clicking on the Sum of VisitID field in the Values box. The menu that appears has options that would let you move the field’s relative position in the box (if there were more than one field) or to one of the other boxes that define the pivot table. But what we want here is to explore the Summarize By option which displays a sub menu that includes five choices as shown in the following figure
By selecting Count from this list, we can display the number of visits by race and by year instead of the sum of the visit id as shown in the following figure.
But sometimes a count is not all you need. Perhaps you want to show the number of visits as a percentage of the total visits in the table or as a percent of the total visits by year. You might think that you will have to resort to a DAX measure expression to build the calculation, but PowerPivot provides several built-in calculations that you apply without knowing any DAX or performing any calculations on your own.
Right click on any of the fields in the pivot table and position your mouse over the Show Values As menu option as shown in the following figure. A fly-out menu appears with 14 built-in calculation methods. Selecting the first one, % of Grand Total compares the value in each cell of the table to the overall Grand Total for all the years and races and reports what percent of that total the current cell represents.
For example, in 2002, 398,759 patient visits were from Hispanics. Compared to the total of 3,460,679 total visits during the period 2002-2003, this represents 11.26%.
In a similar way, we can display the percentage of total visits each race represents in any given year by selecting the % of Column Total calculation as shown in the next figure.
While I will not show it here, we could also look at the percent each cell in the table represents of the row total. Ok, just because you can do a calculation does not mean that you should or even that it makes sense in all cases. In this case, we would be reporting on the percent of visits by each race that occurred in each year. I’m not sure what that would really tell us.
But lets modify our table a little and show races by county as shown in the following figure.
In this case, we have built a simple hierarchy of data with County being at the top of the row hierarchy and race being a subset under county. Now we can ask the question what percent of visits in each county are represented by each race by choosing the option % of Parent Row Total.
Now, each race row shows the percent of visits by that race within that county for that year. Notice that even the county rows are displayed as a percent, but they are a percent of the total visits for the entire year.
Suppose that instead of looking at visits by race by county, we want to look at visits by race by quarter by year. In this case, we might start with the pivot table shown below:
This time, we would select the % of Parent Column Total as shown in this figure.
Another interesting thing you can do is compare one row or one column to another to get the differences or percent differences. Lets return back to the basic pivot table which reports visits by race and year. Right click in any of the data cells in the pivot table and select Difference from in the Show Values as submenu. This option displays a popup dialog asking for the Base Field to define where we want to calculate the difference from. In this case, we want to calculate the difference from one of the years in the report so select Year from the drop down menu. Notice that you can select any of the dimensions used in the pivot table. Next, select the Base Item or the specific value within the dimension that you want to use as the base. If we want to calculate the change in visits from 2002 to 2003, we would select Year as the Base Field and 2002 as the Base Item as shown in the following figure.
Notice that two of the Base item values is (previous) and (next). These options would be useful when comparing the values in any one column to the column immediate to the left or right of the current column rather than to one specific column such as when you select a specific value.
The resulting pivot table shows the number of increased visits in 2003 compared to 2002. If the visits in 2003 were less than 2002, the value would be negative.
In addition these the options shown here, there are other predefined calculated measures that you can use such as running totals and rank. I encourage you to explore what these options can give you before you decide to create custom DAX measures.
Well, that’s all for this week. Next week I look at a few formatting issues before covering how to create custom DAX measures.
See you next week.