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.