# November 2003 MDX Puzzle Solution

41012.zip

Problem: If I told you that this year, sales for my new product line totaled \$1 million, would you say that's good or bad? Without understanding how that number fits in with the rest of my business, you don't know. If you knew how other product lines were selling, what the total sales for my company were, or what this product's sales trend had been, you'd have a context for the \$1 million sales figure.

A common way to put a number into context is to determine how much that number contributes to a greater whole. For example, when you view sales for a product family, you might also view what percent of total sales that product family is. For this month's puzzle, use the FoodMart 2000 Sales cube to write an MDX query that displays the cube's product families on the returned rows and Store Sales and Percent of Total Contribution (to Store Sales) on the columns.

Solution: The first step in answering this question is to create a calculated measure for determining how a given number contributes to a greater whole. In Listing A, I call the calculated measure Percent of Total and define it as the Store Sales for the currently selected product divided by the Store Sales for all products. Because the result is a percentage, I further define Percent of Total by using a format string that displays the result as a percentage to two decimal places of accuracy.

After you define the calculated measure, the rest of the query is straightforward. As the problem specified, I put Store Sales and Percent of Total on the columns and each of the Product Family members on the rows.