On Demand Webinar

Excel - Power Pivot For The Everyday Excel User

Webinar Details $219

  • Rated:
  • Webinar Length: 100 Minutes
  • Guest Speaker:   Mike Thomas
  • Topic:   Business Administration, Business Skills, Software, Taxation and Accounting
  • Credit:   CPE 2.0, ATAOP 1.5, ATATX 1.5
All Access Membership
This webinar introduces you to the DAX formula and function language that is part of Power Pivot. You will learn how to access DAX, understand its syntax and create calculated columns and Measures (a special type of DAX formula)

We hear a lot about the benefits of storing your list-based data in Excel's Data Model instead of in worksheet cells. Most of those benefits revolve around how much data you can store, how much space you can save and how much easier it is to create Pivot Tables from multiple data sources.

But dive a little deeper and you'll find a whole magical world of DAX, Measures and CUBE functions which will help you to take your reporting and data analysis skills to the next level. This really is where the Power of Power Pivot comes in. Think of DAX as Excel formulas on steroids. Without it, you are using a fraction of the capabilities of Power Pivot.

In this training, you will learn, using practical examples, how Power Pivot provides Business Intelligence and reporting functionality within the familiar environment of Excel to provide you with better business insights and enable you to make more informed business decisions.

Why you should attend:
If you want to take your reporting capabilities to the next level by learning how to leverage the functionality of DAX formulas, this is a must-attend training session!

Topics covered:
Introduction to DAX (the Power Pivot formula language)
Manipulating text with a Calculated Column
Performing a Lookup with DAX
Working with dates in DAX
DAX Calculated Columns v Query Editor Calculated Columns
Introduction to Measures
The all-important CALCULATE function
CUBE functions - letting the worksheet talk to The Data Model

Who should attend?
This training is aimed at users who are familiar with the basics of Power Pivot and The Data Model and who wish to take their knowledge and learning to the next stage.
  1. Introduction
  2. The Data Model 00:04:04
  3. Why Use The Data Model? 00:05:57
  4. The “One In A Million Row Limit” 00:06:15
  5. Storage Capacity: Data Model V. Worksheet 00:07:15
  6. No Support For Related Lists 00:08:25
  7. Worksheet Base List Example  00:08:38
  8. Support For Many Data Sources - Databases, Text Files, Web Pages, and Sharepoint - The Data Model 00:10:00
  9. DAX and Cube Functions 00:10:23
  10. Power Pivot 2 Raw Data File 00:12:49
  11. Building A Report 00:16:39
  12. Bringing In Data From Other Sources 00:20:33
  13. How To Get the Data From The Data Model 00:25:29
  14. Power Pivot And The Data Model 00:25:38
  15. Importing Data Into The Data Model 00:29:05
  16. Query Editor 00:34:06
  17. Building a Pivot Table From The Data Model 00:37:58
  18. How To Do Calculations 00:48:12
  19. If You Only Have One Table In The Data Model 00:56:20
  20. DAX - Data Analysis Expressions 01:00:08
  21. Formula Example 01:15:37
  22. How To Create Calculated Columns 01:21:14
  23. The Difference Between Measures and Calculated Columns 01:22:54
  24. Speaker Comments 01:40:18
  25. Presentation Closing 01:41:02
  • Cell 00:04:57, 00:17:12, 00:50:37
  • Column 00:4:58, 00:50:06, 01:17:12, 01:39:05
  • Column Headings 00:39:19
  • Data Model  00:02:49, 00:07:28, 00:10:25, 00:21:59, 00:36:21, 00:40:30, 00:58:29, 01:21:30
  • DAX - Data Analysis Expressions 00:03:00, 00:10:29, 01:00:08, 01:33:42
  • Formula 00:50:44, 01:00:28
  • Pivot Table 00:03:28, 00:05:17, 00:08:38, 00:12:18, 00:12:59, 00:25:51, 00:37:59, 01:15:45
  • Power Pivot 00:01:27, 00:34:44, 01:25:05
  • Power Query 00:29:46
  • Query 00:29:13, 00:34:37
  • Ribbon 00:13:07
  • Row 00:06:22, 00:30:49
  • Table 00:26:47
  • Workbook 00:59:29
  • Worksheet 00:05:57, 00:06:19

Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

Column Headings : The column heading or column header is the gray-colored row containing the letters (A, B, C, etc.) used to identify each column in the worksheet. The column header is located above row 1 in the worksheet.

DAX - Data Analysis Expressions: Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel data models. DAX is a formula language and is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values.

Data Model: A Data Model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook. Within Excel, Data Models are used transparently, providing tabular data used in PivotTables and PivotCharts.

Formula: A formula is an expression which calculates the value of a cell.

Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.

Power Pivot: Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.

Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.

Query: A database query extracts data from a database and formats it in a readable form. A query must be written in the language the database requires; usually, that language is Structured Query Language (SQL). For example, when you want data from a database, you use a query to request that specific information.

Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.

Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.

Text Files : Raw data files that often have file extensions such as .TXT or .CSV. TXT files are sometimes tab-delimited (meaning each field is separated by a tab character) while CSV files are comma-delimited.

Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.

Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.


Guest Speaker

  • Mike Thomas

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or training@auroratrainingadvantage.com.

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.

ATAOP Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.

ATATX Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.