Power Pivot for Microsoft Excel 2016 UsersIf you are trying to use PowerPivot to import the .txt demo file, you will not be able to. You willprobably receive an error message looking like this:By default, PowerPivot is not activated in Excel 2016. There is a simple fix. Go to File > Options >Add-Ins. In the Manage box, click COM Add-ins > Go. Check the Microsoft Office Power Pivotbox, and then click OK. If you have other versions of the Power Pivot add-in installed, those versionsare also listed in the COM Add-ins list. Be sure to select the Power Pivot add-in for Excel. Thefollowing image shows the Excel Options window. You should see Add-ins menu on the left.Advanced can also help.The ribbon now has a PowerPivot tab.If you do not see the tab added to the Excel ribbon automatically, close Excel and start it again. Youshould see PowerPivot tab immediately. If you close Excel in the middle of your work and return, justclick on the ‘Manage Data Model’ tab in PowerPivot window to bring back your tables.You can get more instructions on “Start the Power Pivot in Microsoft Excel add-in” athttps://support.office.com/en-us/article/Start-the-Power-Pivot-in-Microsoft-Excel-add-in-a891a66d36e3-43fc-81e8-fc4798f39ea8.For supplementary readings, please read “Chapter 10: Mashing Up Data with Power Pivot” from thebook on Excel 2016 by Jelen and Michael Alexander (2016), while working on this assignment. Thus,you will have a general understanding of the benefits and drawbacks of Power Pivot as a BI tool, howto enable Power Pivot Add-in from Excel 2016, the differences between Power Pivot and regular pivottables, and using DAX calculations. If you have problems finding a corresponding function icon or toolfor using Power Pivot in a particular version of Excel, the book by Rob Collie and Avichal Singh(2016) should be helpful.ReferencesCollie, R. and Singh, A. (2016). Power Pivot and Power BI: The Excel User’s Guide to DAX, PowerQuery, Power BI & Power Pivot in Excel 2010-2016. Merritt Island, FL: Holy Macro! BooksJelen, B. and Alexander, M. (2016). Excel 2016 Pivot Table Data Crunching. Indianapolis, IN: Que.Part of the MrExcel Library series.Dr. June LuMashing Up Data with PowerPivot in Excel 2016Installing PowerPivotPowerPivot is included in Excel 2013, Excel 2016 (Office 365 Professional version) as a free add-inprogram brought to you by the SQL Server Analysis Services team at Microsoft. To improve Excel asa Business Intelligence tool, PowerPivot makes it possible to do jaw-dropping analyses in Excel. Thisset of hands-0n 4 instructions is for using Power Pivot in Excel 2013/2016. To start PowerPivot inExcel 2013, please follow the instructions at http://office.microsoft.com/en-us/excel-help/start-powerpivot-in-microsoft-excel-2013-add-in-HA102837097.aspx.1. Go to File > Options > Add-Ins from the left column.2. At the bottom, choose Manage box, and select COM Add-ins> Go.3. Check mark Microsoft Office Power Pivot for Excel 2016 box in the list of available COM Addins, and then click OK. If you have other versions of the Power Pivot add-in installed, thoseversions are also listed. Be sure to select the Power Pivot add-in for Excel 2016.4. Power Pivot was first added to Excel 2010 and then to Excel 2013. Now many students use Excel2016 and Power Pivot is also accessible. Honestly, the steps for using Power Pivot in differentversions are very similar.5. If the Power Pivot tab does not appear in the ribbon immediately, close Excel 2016 and thenrestart it. The ribbon now has a Power Pivot tab.Before you start using Power Pivot in Excel 2016, please watch “Basic Excel Business Analytics #41:Excel 2016: Introduction to PowerPivot & Data Model” in YouTube(https://www.youtube.com/watch?v=xVmbMqRGBA8). Now you are ready to learn how to use PowerPivot for business analytics or as a BI tool.Add Excel Data By LinkingThere is a small Excel file that maps StoreID to store name and other relevant data. Moreover, youwant to make sure that if the original Excel worksheet changes, those changes make it through toPowerPivot. To do this, follow the steps below:1. Please open the Excel file Stores.2. Select one cell in the worksheet and press Ctrl+T. Excel asks you to confirm the extent of yourtable and if your data has headers.3. The table gets a default format. You can use the Table Tools Design tab to change that formatif the current one does not look good to you.4. On the left side of the ribbon, you see that this table is called Table1. Type a new name suchas StoreInfo5. On the PowerPivot tab, select Add to Data Model as shown below, the table appears in thePowerPivot window. A link icon should show in front of the sheet tab nameBuilding a PowerPivot ReportThis part will walk you through your first PowerPivot data mash up. In this example, you create areport that merges a 1.8 million row file with a store identifying data in Excel. To import the big datafile into PowerPivot, follow these steps:1. Once Add to Data Model is clicked, the system will create a linked table in Power Pivot windowlike this. If the linked table name is not StoreInfo, you can right click on the sheet tab to rename it.2. If you are not in Power Pivot window, select the Manage icon, the first on the blue ribbon. A newPowerPivot application window appears. PowerPivot offers four tabs: Home, Design, Advancedand Linked Table.3. You want to import your main table first. This is a large data file. While Home tab is activated, youselect From Other Sources, then select Text File from the dialog box. PowerPivot shows theTable Import Wizard.4. Select a Friendly Connection Name, such as Sales History. Click the Browse button and locateyour text file, Demo. Make sure that your Table Import Wizard looks the same as the image below5. If there are any columns that you do not need to import, clear those check boxes. The file is goingto be read into memory. Click Finish and PowerPivot begins loading the file into memory. TheWizard shows how many rows have been fetched so far.6. Click Close to return to the PowerPivot window.7. The data set is shown in the PowerPivot Window. Grab the vertical scroll bar and scroll throughthe records. You can also Sort, change the number format, or filter. If you forgot to name theconnection, you can always use Existing Connections to add the name later.Define Relationship6. You need to link from one column in your main table to a column in another table. To simplifythe relationship process, navigate to your main table, Demo, and select a cell in the columnfrom which you are linking.7. Click on the Design tab in the PowerPivot ribbon.8. Select Create Relationship. The Create Relationship dialog appears. By default, theselected table and column appears in the left column fields, as shown below.9. If you skipped a step and the correct table is not shown in the Table 1 drop-down, then selectDemo from the Table 1 drop-down.10.If you did not select the correct column in the previous step, then open the Column drop-downto select StoreID.11.Open the Table 2 drop-down list to select StoreInfo.12.Because the column names match, PowerPivot automatically selects the related column,StoreID. If not, you can do it yourself. This simple dialog replaces the VLOOKUP function.13.Click OK. You have now created a relationship between the two tables.14.Click Manage Relationships on the ribbon to check it out. You can always Edit therelationships created before.15.Please save all the changes . All the changes in Power Pivotwindow are attached to your original Excel file. So you can stop and continue to work in PowerPivot window of a particular file anytime.Add Calculated Columns Using DAXBefore building the pivot table, use DAX (Data Analysis Expression) formula language in Power Pivotto add a new calculated column to the Demo table. Follow these steps to add a Year field to theDemo table:1. Click on the Demo worksheet tab at the bottom of the PowerPivot window.2. The column to the right of Revenue has an Add Column heading. Click in the first cell of thisblank column.3. Click the icon to the left of the formula bar. The Insert Function dialog appears withcategories for All, Date&Time, Math&Trig, Statistical, Text, Logical, and Filter. SelectDate&Time from the drop-down list. You instantly notice that this is not the same list offunctions in Excel.Luckily, some familiar old functions are in the list as well. Scroll down and select the YEAR function.Click on the first date cell in the Date column. PowerPivot proposes a formula of =year(February 13, 2019).Complete the formula by typing a closing parenthesis and pressing Enter. Excel fills in the columnwith the year associated with the date, as shown below.4. Right-click the column heading and select Rename Column. Type a name such as Year. Thismethod allows you to add as many columns as you like.Build A Pivot TableOne of the advantages of PowerPivot is that multiple tables can share the same data and slicers.Open the PivotTable drop-down on the Home tab of the PowerPivot ribbon. You have many optionsbeyond a single table or chart. Follow these steps:1. Select PivotTable. You now see the PowerPivot tab back in the Excel window.2. Choose to put the pivot table on a new worksheet. The PowerPivot Field List is a third variationof the pivot table field list. It is actually a new entry in the Task Pane. Both tables are availablein the top of the Field List. The main table is expanded to show the field names, but you canexpand the other table and add those fields to this pivot table.3. Select Revenue from Demo in the PowerPivot Field List. Expand the StoreInfo table. SelectRegion from the StoreInfo table. Excel builds a pivot table showing sales by region. At thispoint, you have a pivot table from 1.8 million rows of data with a virtual link to a lookup table.4. You might want to go to the PivotTable Tools tabs to further format the pivot table. Pleaseapply a currency format and rename the Sum of Revenue field and Row Labels, choose aformat with banded rows, and so on.Slicers in PowerPivotTo show some more features of the PowerPivot pivot table, add some slicer functionality. The slicersin PowerPivot are slightly different from slicers in regular Excel.1. Select Insert/Slicer. You will notice that box for Slicer appear. This is used for both Verticaland Horizontal Slicers in the Excel 2010.2. They are great for long lists that might need a scroll bar. Horizontal slicers go above your pivottable.3. Select year and create the slicer. The years appear in a small slicer surrounded by a big box,as shown below. Never try to make that big box smaller3. Follow step 1 to add additional slicers4. Add Division and Era to the Slicers. Resize the slicer as per need. It will look like below5. Add Mall Developer to the slicer, place it on left of the pivot table as it has long list of relativelylong names.6. Slicers work the same as they do in regular Excel pivot tables. Click one item to select it.Ctrl+click additional items to select them as well. See the changes to the pivot table. You canresize the boxes if you want the slicers to take up more or less room. Click the box once andresizing handles appear. To format the slicers, you have to click on the slicer.7. Save and close the Excel file.Two Kinds of DAX CalculationsA DAX function can add a calculated column to a table in the PowerPivot window. The RELATEDfunction can also be used in a calculated column to grab a value from a different table. DAX can alsobe used to create new measures in the pivot table. These functions do not calculate a single cellvalue. They are all aggregate functions that calculate a value for the filtered rows behind any cell inthe pivot table.Using RELATED() to Base a Column Calculation on Another TableThe next several examples make use of a file called WeatherMashup.xlsx. This file started out withgeneric sales by day and by store data set (Sales datasheet). A company sells products both in adifferent sales trends.Suppose you downloaded weather data for the three years of daily dates. Several examples in theDAX Measure calculation mash up the sales and weather data to look for trends.1. Please open the workbook WeatherMashup.xlsx and link the Excel table Sales to the sametable in PowerPivot window (Hint: you’ve learned it in the previous part).2. Use DAX to create a calculated column, WeekdayName, use =FORMAT(Sales[Date],”dddd”)to convert the date to the day of the week.3. Use DAX to create a calculated column, WeekdayID, use =WEEKDAY(Sales[Date],2). The 2argument matches the same function in Excel. 2 numbers the days starting with 1 for Mondaythrough 7 for Sunday.4. Use DAX to create a calculated column, LocationDay, use=CONCATENATE(Sales[Location],Sales[Date]). You see this used later when calculating thedistinct number of store days.5. To calculate sales per store associate, the solution is to use the Related() function. Thisfunction tells DAX to divide by the one value that is related to a particular locationday. Toaccomplish this, a relationship has to be established between Sales and the Staffing table.6. For this purpose, you need to first create a column, LocationWeekDay in Sales, using theformula =Concatenate (Sales[Location],Sales[WeekDayName]).7. Link Sales and Staffing by LocationWeekDay and LocationWeekday. This time, you arecreating a relationship between two tables. Create Relationship icon is in Design menu.8. Create another column in Sales, SalesPerPerson, use the formula = Sales[NetSales]/Related(Staffing[StaffLevel]).9. Hint: You need to create relationships between all the linked tables, before you are able tomove on. Sales and Weather are related by Date. Sales and Weekdays are related byWeekdayID.Count Distinct Using DAXDAX lets you count how many distinct values meet the filter. To create a new calculated field in DAX,use Measures icon in the PowerPivot tab. A calculated field or a new measure is a formula that iscreated specifically for use in a PivotTable (or PivotChart) that uses PowerPivot data. You have tocreate a pivot table as in the following image (Row: Rain, Column: Location, Value: Net Sales)before you can create a new measure.1. When you select Measures icon and click on New Measure, you get the Measure Settingsdialog box open. The Table name should be the base table where your main numerical data islocated. Change the first drop-down from Weather to Sales if necessary.2. For the Measure Name, use a name such as DayCount.3. Use the same name for Description.4. Measures are always aggregate functions, not cell-level functions. Thus, you must use anaggregate function such as SUM or COUNTROWS.5. The magic function here is Distinct (Sales[Date]). For any cell in the pivot table, the distinctfunction returns a list of the distinct values for the rows that match the filter.6. After typing the formula, click the Check Formula button to make sure that your syntax iscorrect.7. You could build SalesPerDay with a single formula: =SUM(Sales[NetSales])/COUNTROWS(Distinct(Sales[Date])). The figure below shows Sales Per Day based onthe amount of rain and the location.There is something wrong with the grand totals in the image above. For the airport, the average of$2665 in sales per day is accurate. However, if the averages for the airport and for the mall arecorrect, the grand total in the lower right corner should not be $5833. How about adding Day CountColumn into the pivot table?The airport location was open for all those years. The mall location opened late in 2006 so there arefewer days for the mall location. The airport is open on Christmas, but the mall is not. Thus, theremany days where only one store is open. The 2nd column shows total sales of both stores. The DayCount column counts a day when either one store or the other was open. Thus, both stores did sell$6.3 million over the course of the data set. However, because both stores were not open for theentire period, the calculation of $6.3 million divided by 1086 days is wrong.The solution is to count the distinct number of a concatenated column of location and date. We haveto count the location days for each store and calculate the sales per store per day.1. Create a new DAX Measure with the formula,LocationDayCount=CountRows(Distinct(Sales[LocationDay])).2. Create a new DAX Measure with the formula, SalesPerStorePerDay= SUM(Sales[NetSales])/CountRows(Distinct(Sales[LocationDay])).3. These two new measures should produce the exact same results for the airport or mall salesper day, the improvement is that the pivot table should show the true average sales per storeper day.When you complete this assignment (your data table should be longer than what shows in the aboveimage), format the values in their proper format, and save each file with your name initials as part ofthe file name (the pattern should be ). Submit the files to therelevant assignment submission dropbox in Learn 9._______________________*: This assignment is adapted from Chapter 10 in Pivot Table Data Crunching: Microsoft Excel 2010written by Bill Jelen and Michael Alexander, 2013, Indianapolis, IN: Que Publishing.