Dax formulas for powerpivot by rob collie pdf

Power Pivot for Microsoft Excel 2016 Users

If you are trying to use PowerPivot to import the .txt demo file, you will not be able to. You will

probably 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 Pivot

box, and then click OK. If you have other versions of the Power Pivot add-in installed, those versions

are also listed in the COM Add-ins list. Be sure to select the Power Pivot add-in for Excel. The

following 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. You

should see PowerPivot tab immediately. If you close Excel in the middle of your work and return, just

click 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” at

https://support.office.com/en-us/article/Start-the-Power-Pivot-in-Microsoft-Excel-add-in-a891a66d-

36e3-43fc-81e8-fc4798f39ea8.

For supplementary readings, please read “Chapter 10: Mashing Up Data with Power Pivot” from the

book 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, how

to enable Power Pivot Add-in from Excel 2016, the differences between Power Pivot and regular pivot

tables, and using DAX calculations. If you have problems finding a corresponding function icon or tool

for using Power Pivot in a particular version of Excel, the book by Rob Collie and Avichal Singh

(2016) should be helpful.

References

Collie, R. and Singh, A. (2016). Power Pivot and Power BI: The Excel User’s Guide to DAX, Power

Query, Power BI & Power Pivot in Excel 2010-2016. Merritt Island, FL: Holy Macro! Books

Jelen, B. and Alexander, M. (2016). Excel 2016 Pivot Table Data Crunching. Indianapolis, IN: Que.

Part of the MrExcel Library series.

Dr. June Lu

https://support.office.com/en-us/article/Start-the-Power-Pivot-in-Microsoft-Excel-add-in-a891a66d-36e3-43fc-81e8-fc4798f39ea8
https://support.office.com/en-us/article/Start-the-Power-Pivot-in-Microsoft-Excel-add-in-a891a66d-36e3-43fc-81e8-fc4798f39ea8
Mashing Up Data with PowerPivot in Excel 2016

Installing PowerPivot PowerPivot is included in Excel 2013, Excel 2016 (Office 365 Professional version) as a free add-in program brought to you by the SQL Server Analysis Services team at Microsoft. To improve Excel as a Business Intelligence tool, PowerPivot makes it possible to do jaw-dropping analyses in Excel. This set of hands-0n 4 instructions is for using Power Pivot in Excel 2013/2016. To start PowerPivot in Excel 2013, please follow the instructions at http://office.microsoft.com/en-us/excel-help/start-power- pivot-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 Add-

ins, and then click OK. If you have other versions of the Power Pivot add-in installed, those

versions 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 Excel

2016 and Power Pivot is also accessible. Honestly, the steps for using Power Pivot in different

versions are very similar.

5. If the Power Pivot tab does not appear in the ribbon immediately, close Excel 2016 and then

restart 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 Power Pivot for business analytics or as a BI tool.

Add Excel Data By Linking There is a small Excel file that maps StoreID to store name and other relevant data. Moreover, you want to make sure that if the original Excel worksheet changes, those changes make it through to PowerPivot. 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 your

table and if your data has headers.

http://office.microsoft.com/en-us/excel-help/start-power-pivot-in-microsoft-excel-2013-add-in-HA102837097.aspx
http://office.microsoft.com/en-us/excel-help/start-power-pivot-in-microsoft-excel-2013-add-in-HA102837097.aspx
https://www.youtube.com/watch?v=xVmbMqRGBA8
3. The table gets a default format. You can use the Table Tools Design tab to change that format if 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 such as StoreInfo

5. On the PowerPivot tab, select Add to Data Model as shown below, the table appears in the PowerPivot window. A link icon should show in front of the sheet tab name

Building a PowerPivot Report This part will walk you through your first PowerPivot data mash up. In this example, you create a report that merges a 1.8 million row file with a store identifying data in Excel. To import the big data file into PowerPivot, follow these steps: 1. Once Add to Data Model is clicked, the system will create a linked table in Power Pivot window

like 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 new

PowerPivot application window appears. PowerPivot offers four tabs: Home, Design, Advanced and Linked Table.

3. You want to import your main table first. This is a large data file. While Home tab is activated, you select From Other Sources, then select Text File from the dialog box. PowerPivot shows the Table Import Wizard.

4. Select a Friendly Connection Name, such as Sales History. Click the Browse button and locate your text file, Demo. Make sure that your Table Import Wizard looks the same as the image below

5. If there are any columns that you do not need to import, clear those check boxes. The file is going to be read into memory. Click Finish and PowerPivot begins loading the file into memory. The Wizard 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 through

the records. You can also Sort, change the number format, or filter. If you forgot to name the connection, you can always use Existing Connections to add the name later.

Define Relationship 6. You need to link from one column in your main table to a column in another table. To simplify

the relationship process, navigate to your main table, Demo, and select a cell in the column from 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, the

selected 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 select Demo from the Table 1 drop-down.

10. If you did not select the correct column in the previous step, then open the Column drop-down to 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 the

relationships created before.

15. Please save all the changes . All the changes in Power Pivot window are attached to your original Excel file. So you can stop and continue to work in Power Pivot window of a particular file anytime.

Add Calculated Columns Using DAX Before building the pivot table, use DAX (Data Analysis Expression) formula language in Power Pivot to add a new calculated column to the Demo table. Follow these steps to add a Year field to the Demo 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 this

blank column.

3. Click the icon to the left of the formula bar. The Insert Function dialog appears with categories for All, Date&Time, Math&Trig, Statistical, Text, Logical, and Filter. Select Date&Time from the drop-down list. You instantly notice that this is not the same list of functions 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([date]). Complete the formula by typing a closing parenthesis and pressing Enter. Excel fills in the column with 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. This method allows you to add as many columns as you like.

Build A Pivot Table One 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 options beyond 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 variation

of the pivot table field list. It is actually a new entry in the Task Pane. Both tables are available in the top of the Field List. The main table is expanded to show the field names, but you can expand 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. Select Region from the StoreInfo table. Excel builds a pivot table showing sales by region. At this point, 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. Please apply a currency format and rename the Sum of Revenue field and Row Labels, choose a format with banded rows, and so on.

Slicers in PowerPivot To show some more features of the PowerPivot pivot table, add some slicer functionality. The slicers in 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 Vertical and Horizontal Slicers in the Excel 2010.

2. They are great for long lists that might need a scroll bar. Horizontal slicers go above your pivot

table. 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 smaller

3. Follow step 1 to add additional slicers 4. Add Division and Era to the Slicers. Resize the slicer as per need. It will look like below

5. Add Mall Developer to the slicer, place it on left of the pivot table as it has long list of relatively long 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 can resize the boxes if you want the slicers to take up more or less room. Click the box once and resizing handles appear. To format the slicers, you have to click on the slicer.

7. Save and close the Excel file. Two Kinds of DAX Calculations A DAX function can add a calculated column to a table in the PowerPivot window. The RELATED function can also be used in a calculated column to grab a value from a different table. DAX can also be used to create new measures in the pivot table. These functions do not calculate a single cell value. They are all aggregate functions that calculate a value for the filtered rows behind any cell in the pivot table.

Using RELATED() to Base a Column Calculation on Another Table The next several examples make use of a file called WeatherMashup.xlsx. This file started out with generic sales by day and by store data set (Sales datasheet). A company sells products both in a different sales trends. Suppose you downloaded weather data for the three years of daily dates. Several examples in the DAX 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 same table 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 2 argument matches the same function in Excel. 2 numbers the days starting with 1 for Monday through 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 the distinct number of store days.

5. To calculate sales per store associate, the solution is to use the Related() function. This function tells DAX to divide by the one value that is related to a particular locationday. To accomplish 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 the formula =Concatenate (Sales[Location],Sales[WeekDayName]).

7. Link Sales and Staffing by LocationWeekDay and LocationWeekday. This time, you are creating a relationship between two tables. Create Relationship icon is in Design menu.

8. Create another column in Sales, SalesPerPerson, use the formula = Sales[Net Sales]/Related(Staffing[StaffLevel]).

9. Hint: You need to create relationships between all the linked tables, before you are able to move on. Sales and Weather are related by Date. Sales and Weekdays are related by WeekdayID.

Count Distinct Using DAX DAX 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 is created specifically for use in a PivotTable (or PivotChart) that uses PowerPivot data. You have to create 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 Settings dialog box open. The Table name should be the base table where your main numerical data is located. 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 an

aggregate function such as SUM or COUNTROWS. 5. The magic function here is Distinct (Sales[Date]). For any cell in the pivot table, the distinct

function 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 is

correct.

7. You could build SalesPerDay with a single formula: =SUM(Sales[Net Sales])/COUNTROWS(Distinct(Sales[Date])). The figure below shows Sales Per Day based on the 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 are correct, the grand total in the lower right corner should not be $5833. How about adding Day Count Column into the pivot table?

The airport location was open for all those years. The mall location opened late in 2006 so there are fewer days for the mall location. The airport is open on Christmas, but the mall is not. Thus, there many days where only one store is open. The 2nd column shows total sales of both stores. The Day Count 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 the entire 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 have to 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[Net Sales])/CountRows(Distinct(Sales[LocationDay])).

3. These two new measures should produce the exact same results for the airport or mall sales per day, the improvement is that the pivot table should show the true average sales per store per day.

When you complete this assignment (your data table should be longer than what shows in the above image), format the values in their proper format, and save each file with your name initials as part of the file name (the pattern should be ). Submit the files to the relevant assignment submission dropbox in Learn 9. _______________________ *: This assignment is adapted from Chapter 10 in Pivot Table Data Crunching: Microsoft Excel 2010 written by Bill Jelen and Michael Alexander, 2013, Indianapolis, IN: Que Publishing.