Pivoting worksheet items

Obviously, the next step should be to populate the worksheet with data, so that you can then play with pivoting. Thinking about the data you would like to see on the worksheet, you most likely would like to see the dollar amount of operations performed during a certain period of time, with the ability to examine that data by salesperson, product, and/or product category. So, the first thing to take care of is to create a calculation determining this amount.

Creating a calculation

The product of the quantity and the unit price of the item in the order details gives you the dollar amount of the operation for that item—and, when summed up over the entire order for each item, it gives you the order total. Summing over all the orders gives you the total sales. Often, though, you need to aggregate sales figures across a certain time period at different levels.

Turning back to our example, let's create a calculation that will calculate the product of the Quantity and the...