Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel 2010: Pivot Tables in Depth, author Curt Frye provides comprehensive, hands-on tutorials on Excel PivotTables, including more advanced techniques such as using macros and the new PowerPivot add-in. The course shows how to connect and consolidate data sources to power PivotTables, sort and filter records, display data in a PivotChart, print tables and charts, and also introduces the DAX language for performing advanced summaries in PowerPivot. Exercise files are included with the course.
When you want to summarize your PowerPivot data you can use the built-in DAX functions to do so. There are two ways to find the functions you need. The first is to use the Help system to look up the function you want and the second is to start typing the function's name and select the proper function from the IntelliSense list that appears. You're probably familiar with Formula AutoComplete and IntelliSense from using Excel, so I'll show you how to use the PowerPivot Help system to get information on the available functions. To begin, in Excel we'll click the PowerPivot tab on the Ribbon and then click the PowerPivot Window button.
And when we do, our data model appears inside of the PowerPivot window. Now to get help in PowerPivot you can click the Help button and it's the blue button with a question mark at the top right corner of the Ribbon. So click that and the SQL Server PowerPivot for Microsoft Excel Help dialog box appears. Then inside the dialog box click the Search tab in the left-hand pane and then type function reference, and then press Enter.
When you do you see list of Topics. And in my topic list, topic number 14 is the Function Reference. I've highlighted it to make it call out and I'll double-click it to make it appear in the right-hand pane of the dialog box. I'll expand the window so that it's much wider and now within the window I can click the DAX Online Function Reference link. When I do that displays the Function Reference page on Microsoft TechNet.
And I'll drag the left-hand side of this window to make it even larger. And now within the reference article you can go to any different section that you want. So let's say for example that you wanted to get information about statistical functions. To do that you can click the Statistical Functions link and you'll see all of the functions that are listed and that you can use here in the article. If you want to go back to the previous page you can click the Back button on the toolbar and it'll take you back to the main page.
And just to see one other section we'll click the Date and Time Functions link, and on that page we get a list of the functions that are available to us. The DAX language doesn't include all of the functions you'll find in Excel but it does offer a wide variety of functions that let you build calculations to summarize your data effectively. I strongly encourage you to read through the DAX Function Reference to become familiar with the functions at your disposal.
There are currently no FAQs about Excel 2010: Pivot Tables in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.