The P04_19.xlsx file contains a Data Model with four related tables about a company’s sales and a single blank worksheet. This problem illustrates a potential problem with pivot tables when there is a many-to-many relationship, in this case between Products and Orders. (The “fix” to this problem is beyond the level of this book, but the issue is indicated here.) Suppose you are asked to find the number of orders that contain a specific product such as Dog Ear Cyclecomputer.
a. The natural way is to create a pivot table with OrderID from the Orders table in the Values area, summarized by Count, and ProductName from the Products table in the Rows area. When you create this pivot table, you should see that the counts are wrong. They show 944, the total number of orders, for each product.
b. Try instead putting OrderID from the Order_Details table in the Values area of the pivot table, again summarized by Count. Provide a convincing argument why these counts are correct. For example, check the records for Dog Ear Cyclecomputer in the Order_Details table.