BYU Student Author: @Christian
Reviewers: @Mike_Paulsin, @Parker_Sherwood
Estimated Time to Solve: 30 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
Overview
You have been hired by Porter Patterson, the owner of Porter’s Plates, to help him with efficiency issues. In essence, Porter wants to find out how well his employees are doing at making him money. He gave you an excel sheet that has lots of tables that seem to be connected somehow, but he hasn’t drawn any relationships yet. Your task is to help him get on his feet by creating the relationships in the tables and then forming the table that he asks for (see instructions 2). Don’t let Porter down!!
Instructions
Link the different sheets together by managing the relationships in PowerPivot. If you do not see “Power Pivot” in your ribbon, go to File > Options > Add-ins > Manage: COM Add-ins > GO > Checkmark: Microsoft Power Pivot for Excel > OK. Look carefully at the relationships between each table to determine what table is getting what information from the related table(s).
Create a pivot table that shows the sum of cash receipts amount with Cust_IDs for rows and Emp_IDs for columns. Filter your table to show only Retailers in the western states (only include Washington, Oregon, Idaho, California, Nevada, Utah, and Arizona)
Data Files
Suggestions and Hints
To link the tables together in requirement 1, go to Power Pivot>Manage. On the Home, you can view the relationships in Diagram View. Another way to access the table relationships is in the Design tab in the relationships portion of the ribbon. All the tables are connected in some way, so be sure to draw all the relationships!
Solution
This was the first time I attempted Pivot Tables and it is definitely a challenge! I’m not sure if I completely solved it, but the video was very helpful!
Challenge45_Dataset_PortersPlates.xlsx (4.2 MB)
2 Likes
This was a fun one! I am very familiar with Pivot Tables but Power Pivot was a nice step up for me. I needed to use the cheat sheet to get the order right in my relationships but I found this a very efficient way to combine relevant data together.
Thanks for this!
0611_Floyd_PortersPlates_TechHub.xlsx (4.2 MB)
2 Likes
Definitely was a challenge, and because it’s been a long time since I’ve done a pivot table, I needed some help from the video, but I learned a lot from doing this!
Challenge45_Dataset_PortersPlates_Jeffrey_Winters.xlsx (4.2 MB)
3 Likes
Challenge45_Dataset_PortersPlates_MTaylor407.xlsx (4.2 MB)
This was a challenge for me but it helped me to understand how tables can work together better!
For this problem, I didn’t rearrange the tables as shown in the solutions video. Instead I just clicked on the relationships button in the Data Tools ribbon. After doing that, the diagram view of my table looked like this
I was able to create the pivot table right after doing that. This was a fun challenge!
Challenge45_Dataset_PortersPlates.xlsx (4.1 MB)
2 Likes
Challenge45_Dataset_PortersPlates.xlsx (4.2 MB)
I haven’t learned how to filter Pivot Tables, but this challenge pushed me to do research. I discovered several correct methods to filter one category of the data, but I found only one manual method to complete this challenge. It took me around 30-40 minutes, but I had lots of fun doing it.
Wyatt_Parkinson_PortersPlates.xlsx (4.2 MB)
Haven’t messed around with power pivot too much. This challenge was quick and easy though once I watched some videos on it.
Had to watch some youtube videos and look to other solutions for help but came out with a much better understanding of pivot tables!
Rich_Porter_TechHub_Challenge_45_Solution.xlsx (4.1 MB)
Time to complete: 40 minutes
Difficulty: Intermediate
I was unable to get the data to work for me even after following the solution. Makes conceptual sense but somehow my relationships must still be off.
Challenge45_Solution_PortersPlates.xlsx (4.2 MB)
