In this project, you will import an Access database into the Data Model and use PowerPivot to create a PivotTable and Pivot Chart using multiple tables. Then you will create a Power View sheet.
For the purpose of grading the project you are required to perform the following tasks:
Start Excel. Open and save the downloaded Excel workbook named GO_e07_Grader_h2.xlsx.
Use PowerPivot to import both tables in the downloaded Access database 7F_Vehicle_Parts.accdb into the Data Model.
Insert a PivotTable in a new worksheet. Rename the new sheet PivotTable.
Place the Supplier field from the Suppliers table in the COLUMNS area. From the Supply Order Summary table, place the Item field in the ROWS area, and the Quantity field in the VALUES area.
In cell B5, use Quick Explore to drill to Supply ID, and then drill to Item.
In PowerPivot, on the Supply Order Summary table, add a calculated column that multiplies [Quantity] * [Cost]. Rename the column Total. Copy the entire worksheet and paste it into cell A2 of the Vehicle Maintenance worksheet in the main workbook.
In PowerPivot, insert a PivotChart in a new worksheet. Rename the new sheet PivotChart.
Place the Supplier field from the Suppliers table in the LEGEND (SERIES) area. From the Supply Order Summary table, place the Total field in the VALUES area, and place the Year Ordered field in the AXIS (CATEGORY) area.
Insert a Power View report. Add the title Vehicle Maintenance Orders 2014 – 2016.
From the Supply Order Summary table, place the Item, Quantity, and Year Ordered fields (in that order) in the FIELDS area. Change the visualization to a Stacked Column chart.
Insert a Map visualization using the City, State, and Supplier fields from Suppliers and the Total field from Supply Order Summary. Resize the map to fill the right side of the canvas. Set the legend to display at the bottom.
Change the Power View worksheet Text Size to 75%. Set the background to Light 1 Center Gradient.
Ensure that the worksheets are correctly named and placed in the following order in the workbook: Power View1, PivotChart, PivotTable, Vehicle Maintenance. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.