Microsoft Office Tutorials and References
In Depth Information
As you can see, for each cell there is a calculation of the number of unique products sold.
Moreover, it is worth noting that the aggregation of distinct count is not the sum. If you look,
for example, at the row for the color Yellow, we have sold 33 distinct products with internal
orders, 22 online, but the grand total of distinct products is 34. In other words, out of 34 yellow
products sold, 22 were sold online, 33 were sold directly, and only one of them has been sold
online and not directly, thus giving you the grand total of 34. This might seems confusing at
first glance, but it is indeed the correct behavior to expect when you are using distinct counts.
From the PivotTable point of view, measures and columns look very similar even if, for what
concerns the internal engine of PowerPivot, they are completely different items. Starting in
Chapter 3, you begin to learn the DAX language and the exact difference between calculated
columns and measures.
Adding More Tables
All the reports shown up to now relied on three tables only, and they already have shown
some interesting data. Nevertheless, the AdventureWorks database contains a lot of other
tables that you can add to the PowerPivot data model to improve reporting. You might have
noticed, for example, that the Products table contains a ProductSubcategoryID. This column
is a key in the ProductSubcategory table, which we have not loaded yet. It happens, in turn,
that the ProductSubcategory table contains a key, called ProductCategoryID, which relates to
the ProductCategory table. This chain of relationships lets us retrieve the product category,
by means of walking two steps, from a product to its subcategory and then from the
subcategory to its category.
Graphically, the relationship can be seen in Figure 2-25.