Homework 10 Instructions

.docx

School

The University of Tennessee, Knoxville *

*We aren’t endorsed by this school

Course

211

Subject

Industrial Engineering

Date

Feb 20, 2024

Type

docx

Pages

1

Uploaded by carterjones2524

Report
f5d6b035aae4318fbd440754b850720064ff7d91.docx Your manager would like to look at data from several different data sources to perform different types of analysis regarding the total sales made by different contractors. You have been given the files in the zipped folder and will need to complete the following steps to prepare the data for analysis. 1. Import and clean up data – Import the data from the “2 – Building Permits” file into the Homework 10 file as a table. Before completing the import, you will need to clean up the data. Transform the data using the following steps prior to importing the data: (Asynchronous video: 16.4 ) a. Extract the text before the “/” delimiter in the “Permit Status” field b. Add a custom column called “Permit” that combines the “Permit Type” field with the “Permit Sub-Type field and adding “ – “ in the middle of these fields. Example: Building Permit – Single Family Dwelling 2. Combine the data in the data model – You will need to create a data model using Power Query/Power Pivot and you must create relationships between each table. Be sure to indicate that the first row includes headers when importing the data into the data model. There should be three tables in total and each of these tables are related via the Primary Key (Unique Identifier) below: (Asynchronous vides: 15.2 & 15.3 ) a. “Work description” from the “2 – Building Permits” file relates to Work description from the “3 – Work Type” file. b. “Work group” from the “3 – Work Type” file relates to “Work Group” from the data included in the Revenue worksheet in the “Homework 10” file. 3. Create Formulas - Create two formulas in new columns in the “2 – Building Permits” worksheet of the data model. Please create the following formulas: (Asynchronous video: 15.4) a. Create a column named “Work Type” and use a DAX function to return the Work Type from the “3 – WorkType” worksheet. b. Create a Column named “Revenue” that calculates the “Estimated Value of Work” from the Building Permits worksheet with the “Unit Revenue” field from the Revenue worksheet. 4. Create Measures – On the Revenue worksheet you will need to create three new measures which can be used to compare the performance of each contractor’s total sales. Please calculate the following measures: (Asynchronous Video: 15.5) a. Total Revenue – Calculate the total amount of Revenue from the “Revenue” column that was created in the “2 – BuildingPermits” worksheet. Format the measure as Currency with no decimals. b. # of Contractors – Count the number of distinct contractors in the “2 – Building Permits” worksheet and format the measure as comma with no decimals. c. Average Revenue per Contractor – Divide the Total Revenue Measure by the # of Contractors measure and format the measure as currency with no decimals. 5. Power Pivot and KPI’s – Create a Pivot Table in Sheet 1 that calculates the total Revenue organized by contractor. For the pivot table please use the work type as a filter and insert a slicer for this filter. (Asynchronous Video: 16.1) Additionally, management would like to compare the contractors to the average Revenue per contractor measure that was created in step 4. To make this comparison, please create a KPI using “Total Revenue” as the base field with a goal of 100,000. Any amount below 50,000 should be in red, and anything above a goal of 100,000 should be in green. Everyone in-between these values will be in yellow. Place this KPI in the pivot table displaying the color that corresponds to the contractor’s performance. (Asynchronous Video: 16.2) 1 | P a g e
Discover more documents: Sign up today!
Unlock a world of knowledge! Explore tailored content for a richer learning experience. Here's what you'll get:
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help