ITM310 - Problem Solving #7 – Working with Mul±ple DataSets in Excel’s

ITM310 - Problem Solving #7 – Working with Multiple DataSets in Excel’s PowerPivot Feature - 25 pts
What is PowerPivot? When working with a single Excel spreadsheet, you are limited in what you can do with the data. We found PivotTables were a better option, as we could view data in several dimensions. The next step in data analysis is a powerful tool available in Excel called PowerPivot. PowerPivot lets you import multiple datasets from various sources (such as separate spreadsheets or query results from a database), and then view all of the imported files as a set of “data tables.” The imported tables may include formulas and expressions that allow more sophisticated data calculations and analysis.
This assignment is to be completed individually. Note that PowerPivot is included in Excel Professional 2013, including the Office 365 available for students through OIT (see Course Resources). It is not included in student versions of Office, or Mac Office versions of Excel, but is coming in Mac Office 2016. PowerPivot is available for Excel 2010 Professional as a download from Microsoft. All instructions below are written for Excel 2013 & menu items vary slightly for 2010. See supplemental software document and menu graphics for differences.

There are two steps to this activity: 
    1. Set up your Excel PowerPivot working file - give yourself an extra half hour to go through the PowerPivot setup before you begin your problem tasks. 
    2. Complete the tasks on the last page of this document in Step 9.
Before you Begin: See Excel podcast ITM310_Intro_to_PowerPivot.mp4), and review notes. Download the zip file which contains three Excel data files (ProductData.xlsx, SalesData.xlsx, and TerritoryData.xlsx), plus the Installing PowerPivot in Excel Office 20xx.doc for either Office 2013 or Office 2010. Have these files available on your computer desktop, in your homework file, or USB drive before you begin the PowerPivot setup.
Instructions:  Follow the instructions below carefully to set up a new PowerPivot working model by importing the three separate Excel worksheets and establishing a relationship between them. When finished with your PowerPivot setup, complete the two problem tasks located on the last page of this document. We will use PowerPivot again later in the semester for your final semester BI project so this activity is great practice!
Note: If you begin your setup in Excel 2013, you cannot then work with the file in Excel 2010, and vice versa. For best results, complete your setup and homework problems on the same computer, and save your working file in case you need to come back to it. 
 
Step 1: Open a new Excel file & check to see if PowerPivot is installed on your copy of Excel. If it is not shown in the menu as below, install the PowerPivot function in Excel Professional 2013 or 2010, using the appropriate file included with this activity. 
You can instantly tell if PowerPivot is already installed in Excel. Open a new worksheet, and look for a top Menu item that says “PowerPivot.” If it is not there, install it using the “Installing PowerPivot” file from Blackboard noted above for your version of Excel and continue to Step 2.

Excel PowerPivot is built in to Excel 2013 Pro, and a download into Excel 2010.
   

Step 2.  Create PowerPivot Data Model

Note: PowerPivot opens in a second worksheet window when you click the Manage icon. Both the PowerPivot Data Model and the regular Excel windows will be open while you work. 
a.    Open a new Excel file open, click on the POWERPIVOT tab (1) in the top menu; a new menu appears. Double-click the green cube “Manage Data Model” (2) to open a PowerPivot window (two windows will be open, your original Excel worksheet, and the new PowerPivot window. 

Step 3.  Import Data from External Files

From your PowerPivot window you can import data from many sources. We will import three tables from three Excel spreadsheets. Download these files from Blackboard if you have not already done so.

a.    Import Data from SalesData.xlsx Excel file
1.    On the Ribbon to the right of the Manage icon, click on “From Other Sources.” 

2.    Scroll to the bottom of the Table Import Wizard and choose “Excel File.” Press Next >
3.    Click “Browse” to find SalesData.xlsx on your desktop, USB drive or folder & Open. 
4.    IMPORTANT!! Check the box “Use first row as column headers.” then press “Next”
 
5.    Press Next >
6.    Check the box for the source table (SalesData$)
 

7.    Press “Finish.” You should see a “Success,” importing 4,999 rows. Then “Close.” You should now see records from the SalesOrder spreadsheet and a worksheet tab at the bottom of  your Excel PowerPivot file. We’ll refer to these new worksheets as tables as we continue work with them. 
 

b.    Import Two More Spreadsheets files
1.    Repeat the steps above to import two more datasets into your Excel PowerPivot file
a)    ProductData.xlsx  (92 rows)
b)    TerritoryData.xlsx (10 rows)
After importing the two additional files, you should see 3 worksheet tabs at the bottom left with the name of each of the imported datasets, representing our 3 new PowerPivot tables. Click on the tabs to switch between them. 
 

Step 4.  Build Relationships between the dataset tables

a)    Because PowerPivot views each imported dataset as a table, it also depends on “relationships” to be able to join the tables together. We will learn more about relationships when we start working with Access, but for now, think of a relationship as a way to link two different tables together by matching common fields; when working with databases, the database management system can retrieve data from different tables and combine them into a single record using that matching field. PowerPivot works like this also. 
 
b)    Click on “Diagram View”  (see below)
c)    You will see the three imported datasets as boxes (tables). There are no connections yet between them, but they may be moved, or enlarged, to see more fields.  

d)    VERY CAREFULLY create two relationships.  
i)    First, place your cursor on the SalesData field called ‘ProductID,’ depress your left mouse button to select, and drag it over on top of the ‘ProductID’ field in the ProductData table.  
ii)    Then, repeat for the ‘TerritoryID’ field, from SalesData to TerritoryData.   
 
You now have relationships between the matching fields in the tables that allow us to pull data from any of the three tables as we begin our work.

e)    Return to Data View (see icons in the top right)
 

Step 5.  Create Calculated Columns 

Calculated Columns are columns you add to a table that contain calculated expressions, similar to what we did in earlier problems when we added a totals column to a worksheet. These columns contain special calculations or formula. They can be used in PivotTables as Filters, Rows, Columns, or Values. You will create three of calculated columns in the SalesData table.  

a)    Open the SalesData table by clicking its tab in the lower left corner of your workbook.
 

b)    Scroll to the far right to the first empty column, and click in the first empty cell under the title “Add Column” 
c)    On the formula bar, copy and paste:      =[OrderQty]*[UnitPrice]

d)    Press Enter. If entered correctly, the calculated values will be entered into the column cells for each row.
e)    Double click on the green column header, and replace the CalculatedColumn1 header with LineTotal
 

f)    Create two more Calculated Columns in the SalesData table by copying and pasting the expressions below into the next two empty columns & update the column headers.

(1)    SalesCost will use the formula:     

 =RELATED(ProductData[StandardCost])*[OrderQty] 
 
(in this case we need to pull the Standard Cost for each product we sell from the ProductData table, which is related to the SalesData table.

(2)    Margin is calculated by:                                =[LineTotal]-[SalesCost]

Your completed calculated columns should look like those below & contain calculated values. 

Step 6.  Add Calculated Fields 

Calculated Fields in PowerPivot (also called Measures) are different from Calculated Columns. Calculated Fields use the powerful DAX (Data Analysis Expressions) formula language that does many things regular Excel formulas cannot.
  
Calculated Field measures can only be used in your PivotTables/Charts as Values. They are always numbers. When you drop one into a PivotTable, it recalculates itself specifically for the cell where it is located. 
   
There are four calculated fields to create in the SalesData table, in the Calculation Area at the bottom of the worksheet. For best results, copy and paste the exact formula from this document into your Excel PowerPivot file.

a)    Continue on with the open SalesData table. 
b)    The Calculation area is the area of blank rows at the bottom of the worksheet. This is where you create Calculated Fields. It does not matter where you place your new Calculated Fields, but it is easiest to read them if you put them in the same column. (If no blank rows are displayed, click on the “Calculation Area” icon in the ribbon menu bar.)
 

c)    Create a ‘GrossRevenue’ Calculated Field
1)    While LineTotal is the total dollar amount for one line of a sales order, GrossRevenue is the sum of all the LineTotals for a given period, i.e. Total Sales. 

2)    Copy this equation:       GrossRevenue:=sum(SalesData[LineTotal])

3)    Click on a blank cell in the Calculation Area (it must be in a column with data in it). 
4)    Then Right Click in the Formula Box,  and select Paste (see next graphic)
5)    If it is a number, format the cell with a Right Click and choose Format to select correct decimal values and currency for dollars.  
 
If your formula is correct, you will see the result $9,912,567.93. This is based on the sum of LineTotal across all of the SalesData rows; in other words, GrossRevenue or total sales. Later when we place ‘GrossRevenue’ in a PivotTable, it will identify which PivotTable cell it is in, and scale itself appropriately, for example, for a single territory.

d)    Create three more Calculated Fields in the cells below GrossRevenue using the formula in the table below. Hint: Copy and paste each formula into the Formula Box to make sure they are exactly correct. Notice there are no spaces) 
    Measure    Formula 
(Copy the entire cell as is from this column only)    Note
x    Gross Revenue    GrossRevenue:=sum(SalesData[LineTotal])    Total dollars of sales received
add    GrossCOGS    GrossCOGS:=sum([SalesCost])    Total standard costs of the units sold
add    GrossMargin    GrossMargin:=[GrossRevenue]-[GrossCOGS]    Total dollars of profit 
add    PercentMargin    PercentMargin:=DIVIDE([GrossMargin],[GrossRevenue])    The percentage of total sales that is profit 

Step 7.  Save Your Data

a)    Click on the Save button, and name your PowerPivot work file:   “SalesDataPowerPivot_your name.xslx”

Remember that when working in PowerPivot, you have two windows that work closely together saved in the same file. One is the PowerPivot for Excel window that contains the Data Manager. The other is the regular Excel spreadsheet window. When you save one both are saved, and you can move freely back and forth between them.

Step 8.  Create a practice PivotTable from the PowerPivot Window
a)    In the PowerPivot window, Home menu, click on the PivotTable icon to create your first PivotTable in the spreadsheet.
b)    To create more PivotTables, you may either copy and paste the pivot table within the Excel Spreadsheet, or return to the Data Management Window to create more.
     
 

You have now returned to the familiar land of PivotLandia !!!  

The one main difference with these new PivotTables is that instead of a single field list from the worksheet selected, you will see all of the tables that we imported into PowerPivot and linked with a relationship. Under each table name is a list of the fields from that table, in addition to any Calculated Columns or Calculated Fields that you added. Here you see the three tables, with the TerritoryData table opened to reveal its fields.
 

Populating the PivotTable is similar to your earlier PivotTable work. The differences are that you may mix and match fields from any of the tables in a single PivotTable. You may also use the Calculated Fields “as is,” that is, they do the work for you in bringing in the calculated results from the expressions created in each table.

To create more PivotTables … select one entire PivotTable (or Chart), then copy and paste it to a new worksheet (or a fresh area of the same worksheet). Or, click on the PivotTable icon in the PowerPivot window – always start from the PowerPivot window to be able to access all the data tables!

To Create a PivotChart … first create a PivotTable, then create a PivotChart based on that table as you have done before. Simply click anywhere in your completed PivotTable, then click on INSERT > Recommended Charts, select a chart, and press OK. From that point on, any changes you make to that PivotTable are immediately reflected in the PivotChart (and vice versa.)

See sample below:

Step 9. Use PowerPivot Excel file to Complete Data Tasks (Homework Tasks begin Here)
Now that you have your PowerPivot Data Model set up, work with your Excel PowerPivot file to complete the following tasks. When your analysis work is complete in the PowerPivot file, copy and paste your resulting PivotTables below the task, and add your written responses below the table.
Note: To expedite grading, please submit just your results section only from this page and on, along with your Excel PowerPivot file back to Blackboard. Either paste the content from this point into a new Word doc, or rename this document as PS#7_yourlastname.docx, and simply delete pages 1-11.

Scenario: Over the last few weeks you have been working with AdventureWorks sales data to provide output to answer common business questions. Your manager is now interested in a more detailed look at how well sales are doing by product and by territory. A critical item is “margin,” the $ contribution to bottom line profit that is available when we subtract the cost of producing (Cost of Goods Sold) an item from its sales price (UnitPrice for 1 item, or GrossRevenue, for a total sales). We may be interested in margin at the $ level, or as a % of sales. 
Tasks: 
1.    Create a PivotTable showing totals for GrossRevenue, GrossCOGS, GrossMargin and PercentMargin by Territory. Paste the table below, followed by a one paragraph written description of AdventureWorks current financial state – are they profitable? Use selected numbers to demonstrate your points.

2.    Create a PivotTable to examine GrossMargin by Territory (rows) and Product Category (columns), and answer the following questions:
b)    Which is the most profitable Territory?
c)    What is the most profitable Product Category?  Which Territory performs the best in that Product Category?