Setting up your Data
We’ll load up the data from the sample ‘Stores’ Excel file so we can manipulate it.
1. Click on the PowerPivot menu and then click on Manage.
2. Navigate to Get External Data -> From Other Sources -> Excel File. Click Next.
3. Browse for the sample data file and be sure to check the ‘use first row as header’ checkbox.
4. Click Test Connection and verify Excel can connect. Click Next.
5. Excel will show you the table it will be pulling data from. You can further filter the data by clicking the Filter button (Figure 3). 6. Click Finish and the Close. Your data should now show up in the Manage Data window (Figure 4). For our scenario we want to investigate the relationship between the number of employees a store has and the number of acres of land served by the store. To do this we need to create a calculated column dividing the number of acres by the number of employees.
1. To do this, click Insert Function.
2. Select the Divide function under All Functions.
3. You will see a new column added to the data model. Select the SellingAreaSize column, add a comma and then select the EmployeeCount column. Hit enter and the column will calculate the values (Figure 5). 4. Double click on the heading for the new column and give it a proper title.