Pivot Table
demmyemi 
DYS546_course_project_Part_Two.xlsxInstructions
| Course Project | |
| Part Two: Creating Interactive Dashboards | |
| Instructions: | |
| Using the data model (relationships), complete the following in Sheet 1: | |
| 1. Illustrate Sales as values in the pivot table. | |
| 2. Illustrate Sales Rep Name in the columns of the pivot table | |
| 3. Illustrate Dates in the rows of the pivot table | |
| 4. Set the Date (grouping) to display Month as subtotals instead of days. | |
| 5. Make a slicer available for Company Name to the right of the pivot table. Select the first four firms | |
| 6. In the Pivot Table Fields List, set the values field to display average sales rather than sum of sales. | |
| 7. Be sure to properly format numbers and edit labels in the pivot table. | |
| 8. Complete this pivot table by adding a complementary pivot chart of your choice to further illustrate the data in this model. | |
| 9. See if you can suppress the value field button on the chart and add a clear title. | |
| 10. Name this sheet Monthly Sales | |
| In Sheet 2 adjust the Pivot table: | |
| 1. Place Company Codes in the filter section of the pivot table fields list. | |
| 2. Filter by selecting just the first ten company codes on the list. | |
| 3. Create a pivot chart (column) with this information | |
| 4. Name this sheet Sale by Rep | |
| Using the Pivot charts in all three Pivot table tabs, create a new tab with which to design a dashboard. Name the tab Dashboard. | |
| 1. First, place a copy of each of the three charts on the dashboard tab. Be sure to unfilter company codes in Sales by Rep pivot table before proceeding. | |
| 2. Next, add two Slicers on the dashboard with connections to certain visuals: | |
| 3. One slicer should be Company Codes and the other Sales Reps. | |
| 4. Connect the first slicer to the charts from the Monthly Sales Tab and the Sales by Rep Tab. | |
| 5. Connect the Sales Reps slicer to chart from Monthly Sales Tab only. | |
| Select the first three choices in each slicer to change the illustration. | |
| To complete this project, save the Sales Rep ID.accdb and Sales Report.txt files to your desktop. | |
Sheet1
Sales Report
| Date | Company Code | Sales Value ($) | REP ID | Previous Purchase Sales Value |
| 01-Mar-18 | 012665-0696 | 571,907.00 | 1608112694700 | 514,769 |
| 02-Mar-18 | 017279-9751 | 908,223.00 | 1608112694701 | 616,821 |
| 03-Mar-18 | 017951-2892 | 641,369.00 | 1608112694702 | 492,119 |
| 04-Mar-18 | 026303-7855 | 577,716.00 | 1608112694703 | 867,626 |
| 05-Mar-18 | 027634-7796 | 161,281.00 | 1608112694704 | 200,686 |
| 06-Mar-18 | 028002-7160 | 806,884.00 | 1608112694705 | 185,775 |
| 07-Mar-18 | 046222-2654 | 351,336.00 | 1608112694706 | 828,721 |
| 08-Mar-18 | 052951-9050 | 320,881.00 | 1608112694700 | 267,078 |
| 09-Mar-18 | 067096-1069 | 552,533.00 | 1608112694701 | 481,587 |
| 10-Mar-18 | 084086-5968 | 668,608.00 | 1608112694702 | 351,336 |
| 11-Mar-18 | 106511-0353 | 495,965.00 | 1608112694703 | 465,029 |
| 12-Mar-18 | 129555-6904 | 804,274.00 | 1608112694704 | 800,503 |
| 13-Mar-18 | 136311-8868 | 514,769.00 | 1608112694705 | 917,877 |
| 14-Mar-18 | 148192-3694 | 876,185.00 | 1608112694706 | 692,981 |
| 15-Mar-18 | 152508-4438 | 917,877.00 | 1608112694700 | 750,374 |
| 16-Mar-18 | 157944-5931 | 581,749.00 | 1608112694701 | 795,035 |
| 17-Mar-18 | 163447-5568 | 481,587.00 | 1608112694702 | 583,299 |
| 18-Mar-18 | 163571-5715 | 158,831.00 | 1608112694703 | 955,582 |
| 19-Mar-18 | 174597-7650 | 238,608.00 | 1608112694704 | 190,357 |
| 20-Mar-18 | 182309-5037 | 234,673.00 | 1608112694705 | 320,881 |
| 21-Mar-18 | 189292-5452 | 267,078.00 | 1608112694706 | 804,274 |
| 22-Mar-18 | 206667-1112 | 443,826.00 | 1608112694700 | 527,164 |
| 23-Mar-18 | 208320-2024 | 616,821.00 | 1608112694701 | 571,907 |
| 24-Mar-18 | 248404-1708 | 556,759.00 | 1608112694702 | 222,368 |
| 25-Mar-18 | 259822-4844 | 200,686.00 | 1608112694703 | 234,673 |
| 26-Mar-18 | 267214-4298 | 429,206.00 | 1608112694704 | 690,079 |
| 27-Mar-18 | 269465-3961 | 717,069.00 | 1608112694705 | 806,884 |
| 28-Mar-18 | 272545-9792 | 335,818.00 | 1608112694706 | 523,762 |
| 29-Mar-18 | 298203-3140 | 523,762.00 | 1608112694704 | 581,749 |
| 30-Mar-18 | 304466-7982 | 430,768.00 | 1608112694705 | 841,313 |
| 31-Mar-18 | 306039-4099 | 222,368.00 | 1608112694706 | 169,440 |
Sheet2
| Sum of Previous Purchase Sales Value | Rep ID | |||||||||||||||||||||||||||||||
| Company Code | A Corp. | Diam Company | Diam Eu Foundation | Donec Limited | Duis Dignissim Tempor LLC | Eget Consulting | Eget Odio Consulting | Elit Institute | Enim Etiam Ltd | Etiam Imperdiet Corporation | Felis Nulla Foundation | Iaculis Consulting | In Faucibus Orci Corp. | In Nec Orci PC | Ipsum Non Arcu Limited | Lobortis Quis Industries | Magna Inc. | Mauris Blandit Enim Company | Metus Facilisis Foundation | Nibh Quisque Nonummy LLC | Non Cursus Corp. | Non Dui Corp. | Non Incorporated | Nonummy Institute | Orci LLC | Purus Mauris Institute | Sapien Nunc PC | Semper Limited | Ullamcorper Duis Company | Ut LLC | Vivamus Industries | Grand Total |
| Ethan | 514,769 | 267,078 | 527,164 | 750,374 | 2,059,385 | |||||||||||||||||||||||||||
| Fatima | 351,336 | 492,119 | 222,368 | 583,299 | 1,649,122 | |||||||||||||||||||||||||||
| Julian | 690,079 | 200,686 | 800,503 | 190,357 | 581,749 | 2,463,374 | ||||||||||||||||||||||||||
| Lenore | 320,881 | 917,877 | 806,884 | 185,775 | 841,313 | 3,072,730 | ||||||||||||||||||||||||||
| Maile | 481,587 | 571,907 | 616,821 | 795,035 | 2,465,350 | |||||||||||||||||||||||||||
| Owen | 169,440 | 523,762 | 828,721 | 692,981 | 804,274 | 3,019,178 | ||||||||||||||||||||||||||
| Roanna | 955,582 | 867,626 | 465,029 | 234,673 | 2,522,910 | |||||||||||||||||||||||||||
| Grand Total | 351,336 | 492,119 | 320,881 | 481,587 | 169,440 | 690,079 | 523,762 | 828,721 | 917,877 | 514,769 | 200,686 | 800,503 | 806,884 | 222,368 | 267,078 | 190,357 | 571,907 | 955,582 | 527,164 | 867,626 | 616,821 | 465,029 | 581,749 | 692,981 | 234,673 | 185,775 | 841,313 | 750,374 | 804,274 | 583,299 | 795,035 | 17,252,049 |
Company Data
| Company | Company Code |
| Non Sollicitudin A Limited | 977372-7467 |
| Sit Amet Institute | 667177-2868 |
| Duis Dignissim Tempor LLC | 306039-4099 |
| Nulla PC | 894439-5907 |
| Elit Dictum Eu LLC | 743066-5302 |
| Purus Mauris Institute | 028002-7160 |
| Vel Vulputate Corp. | 574697-0762 |
| Lobortis Quis Industries | 174597-7650 |
| Arcu Vestibulum Ante Limited | 449019-8027 |
| Felis Nulla Foundation | 027634-7796 |
| Suscipit Associates | 969012-1430 |
| Rutrum Magna Cras Limited | 374133-1445 |
| Mauris Corporation | 859464-7490 |
| In Nec Orci PC | 248404-1708 |
| Orci LLC | 259822-4844 |
| Ornare Lectus Justo Ltd | 809754-3691 |
| Torquent Per Conubia Incorporated | 828889-0216 |
| Suspendisse Institute | 369263-1504 |
| Proin Ultrices Associates | 635558-8747 |
| Ipsum Non Arcu Limited | 052951-9050 |
| Venenatis Vel Corp. | 446499-4203 |
| Massa Corp. | 665311-2380 |
| Dui Cras Pellentesque Incorporated | 679826-6182 |
| Fermentum Arcu Consulting | 893267-7282 |
| Dignissim Magna A Inc. | 444677-0077 |
| Et Ultrices Incorporated | 783967-4731 |
| Diam Eu Foundation | 182309-5037 |
| Lobortis Quis Associates | 683316-4863 |
| Duis Sit LLC | 997274-7522 |
| A Corp. | 084086-5968 |
| Senectus Inc. | 847651-3539 |
| Enim Industries | 388224-2088 |
| Eget Varius LLP | 835654-6161 |
| Lobortis Ultrices Industries | 783553-4210 |
| Dapibus Ligula LLC | 845817-6875 |
| Libero Donec Industries | 457207-5580 |
| Ipsum Curabitur Company | 675703-7251 |
| Sagittis Lobortis Incorporated | 797030-5608 |
| Non Dui Corp. | 106511-0353 |
| Donec Limited | 067096-1069 |
| Vitae Orci Limited | 307971-2711 |
| Amet Orci Consulting | 604717-9871 |
| Diam Company | 017951-2892 |
| Penatibus PC | 613391-7069 |
| Etiam Imperdiet Corporation | 012665-0696 |
| Eget Odio Consulting | 272545-9792 |
| Metus Facilisis Foundation | 206667-1112 |
| Metus Limited | 986874-9335 |
| Feugiat Corp. | 854981-5317 |
| Enim Curabitur Massa Corp. | 319870-2643 |
| Scelerisque Mollis Corp. | 737434-3239 |
| Magna Inc. | 208320-2024 |
| Eros Industries | 329076-2917 |
| Ipsum Suspendisse Sagittis Limited | 762217-0160 |
| Non Incorporated | 298203-3140 |
| Ut LLC | 163447-5568 |
| Ut Cursus Luctus Inc. | 418483-1578 |
| Non Cursus Corp. | 017279-9751 |
| Velit Justo Industries | 962513-2619 |
| Est Congue A Foundation | 677107-6186 |
| Penatibus Et Magnis Company | 803709-7071 |
| Vitae Erat Vel Limited | 493471-6111 |
| Sapien Molestie Company | 704442-8535 |
| Eget Consulting | 267214-4298 |
| Sit Amet PC | 397104-6317 |
| Nonummy Institute | 148192-3694 |
| Eu Limited | 745671-1972 |
| Lacus LLC | 903455-8214 |
| Sed Molestie Consulting | 708365-8612 |
| Eget Venenatis LLC | 518692-9732 |
| Gravida Praesent Eu PC | 949855-0004 |
| Semper Limited | 152508-4438 |
| Ut Dolor Ltd | 509662-7897 |
| Faucibus Ut Nulla Ltd | 966908-5533 |
| Consectetuer Euismod LLP | 999539-3387 |
| Vivamus Industries | 157944-5931 |
| Lorem Tristique LLC | 635926-5466 |
| Iaculis Consulting | 129555-6904 |
| Ullamcorper Duis Company | 189292-5452 |
| Porttitor Scelerisque Neque Company | 984880-8995 |
| In Faucibus Orci Corp. | 269465-3961 |
| Elit Institute | 046222-2654 |
| Dolor Inc. | 648589-8131 |
| Sapien Nunc PC | 304466-7982 |
| Nam Company | 812196-9318 |
| Nibh Quisque Nonummy LLC | 026303-7855 |
| Nec Euismod Company | 387891-2637 |
| Ut Eros Non PC | 802234-2300 |
| Eu Eleifend Company | 449873-7768 |
| Suspendisse Inc. | 933165-8881 |
| Et Netus Corp. | 616878-0077 |
| Enim Etiam Ltd | 136311-8868 |
| Nascetur Ridiculus Company | 454008-4896 |
| Suscipit Est Ac Inc. | 857471-0227 |
| Justo Praesent Luctus PC | 935471-6582 |
| Erat Consulting | 475806-5876 |
| Arcu Imperdiet Company | 632304-1134 |
| Gravida Nunc Sed Incorporated | 566317-7557 |
| Mauris Blandit Enim Company | 163571-5715 |
| Sed Diam Lorem Associates | 354019-1727 |
NumberofOrders
| Count of Date | Column Labels | |||||
| Row Labels | A Corp. | Diam Company | Diam Eu Foundation | Donec Limited | Duis Dignissim Tempor LLC | Grand Total |
| 1608112694701 | 1 | 1 | ||||
| 1608112694702 | 1 | 1 | 2 | |||
| 1608112694705 | 1 | 1 | ||||
| 1608112694706 | 1 | 1 | ||||
| Grand Total | 1 | 1 | 1 | 1 | 1 | 5 |
Number of Orders by First 5 FIrms
A Corp. 1608112694701 1608112694702 1608112694705 1608112694706 1 Diam Company 1608112694701 1608112694702 1608112694705 1608112694706 1 Diam Eu Foundation 1608112694701 1608112694702 1608112694705 1608112694706 1 Donec Limited 1608112694701 1608112694702 1608112694705 1608112694706 1 Duis Dignissim Tempor LLC 1608112694701 1608112694702 1608112694705 1608112694706 1
Sale Rep ID
| Sales Rep | REP ID |
| Ethan | 1608112694700 |
| Maile | 1608112694701 |
| Fatima | 1608112694702 |
| Roanna | 1608112694703 |
| Julian | 1608112694704 |
| Lenore | 1608112694705 |
| Owen | 1608112694706 |
