A Simple report creation using Calulated Field in Data Model

Here we are using 3 tables  from Database AdventureWorkks2008r2

1. Product
2. ProductCategory
3. ProductSubCategory

• Open Power Pivot window, load three tables as discussed in my earlier blog.

(As Relationship already present in database, Power Pivot creates them during loading process)

• Now create a calculated field as we need to count no of products using below mentioned definition:

                      NumofProducts:=CountRows(Product)

 

 

 





• Now simply create a pivot table, put category name from ProductCategory table into rows and choose Calculated field 'NumofProducts' for values.

 

 

 

Advantages of using Data Model:

 

1. Using Data Model Approach, one did not need to use vlookup to create columns. Here Pivottable slice products using category name based on relationship.
2. CalculatedField  created is global for data model. It an be accessed by different Pivottables based on same data model.
3. Regarding speed and memory usage, data model is capable of holding hundreds of millions of rows and computing very complex Pivottables in a matter of seconds.