Excel: Power Pivot For The Everyday Excel User

Access this expert-led webinar instantly, available anytime on-demand.

Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

This webinar introduces the DAX (Data Analysis Expressions) formula and function language that powers Excel's Power Pivot. Participants will learn how to access DAX, understand its syntax, and create both Calculated Columns and Measures—a specialized type of DAX formula used to perform advanced calculations and analysis within the Data Model.

Many Excel users are familiar with the advantages of storing list-based data in the Data Model rather than worksheet cells. These benefits include the ability to manage larger datasets, reduce workbook size, and create PivotTables from multiple data sources with greater efficiency. However, beyond these foundational capabilities lies the true power of Power Pivot: DAX, Measures, and CUBE functions. These tools enable users to build sophisticated reporting solutions and perform deeper data analysis, helping transform raw data into meaningful business insights. Think of DAX as Excel formulas on steroids—without it, you are utilizing only a fraction of Power Pivot's capabilities.

Through practical, real-world examples, this training will demonstrate how Power Pivot delivers powerful business intelligence and reporting functionality within the familiar Excel environment. Attendees will gain the knowledge needed to create more advanced reports, improve analytical capabilities, and make more informed business decisions.

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.

Your Benefits For Attending
  • Understand the fundamentals of DAX and how it extends the capabilities of Power Pivot.
  • Learn to create and use Calculated Columns and Measures for advanced reporting and analysis.
  • Gain practical experience working with key DAX functions, including text manipulation, lookups, date calculations, and CALCULATE.
  • Discover how CUBE functions enable Excel worksheets to interact directly with the Data Model.
  • Improve your ability to generate meaningful business insights and support better decision-making through data analysis.

By attending this webinar, you will develop the skills needed to unlock the full potential of Power Pivot and elevate your Excel reporting capabilities. The practical techniques covered can help you create more dynamic analyses, build more effective reports, and gain greater value from your organization's data.

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 vs. 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 designed for users who are familiar with the basics of Power Pivot and the Data Model and are ready to take their knowledge and skills to the next level.

Level: Basic
Format: Live Webcast
Instructional Method: QAS Self-Study (Traditional)
NASBA Field of Study: Computer Software & App (2 hours)
Program Prerequisites: None
Advance Preparation: No
  1. Introduction
  2. Topics to Cover 00:02:19
  3. Sales .CSV File 00:03:31
  4. Importing Data Into The Data Model 00:04:26
  5. Power Pivot And The Data Model 00:06:16
  6. How To Create Calculated Columns 00:11:29
  7. Building a Pivot Table From The Data Model 00:11:50
  8. How To Create A Column 00:16:17
  9. Creating A Formula 00:17:17
  10. Example Of DAX 00:19:58
  11. Creating Another Column 00:21:14
  12. Using The DAX IF Function 00:23:09
  13. Creating A New Pivot Table 00:26:16
  14. Using The Round Function In A Formula 00:28:43
  15. Adding More Data Into The Data Model 00:32:19
  16. Create A Pivot Table Showing Revenue Per Product 00:34:43
  17. Revenue Per Day 00:38:32
  18. Updating The Data In The Data Model 00:44:04
  19. Calculated Columns File 00:46:28
  20. Creating A Slicer 00:46:57
  21. LOOKUP In DAX 00:48:07
  22. Bringing In Data From Other Sources Into The Data Model 00:52:53
  23. Currency Review 00:56:51
  24. Slicer Review 00:59:08
  25. Measures 01:02:36
  26. Another Reason To Use Measures 01:16:56
  27. Creating Total Profit With A Measure 01:24:22
  28. How To Arrange A Pivot Table By Most Profitable Store, Not Alphabetical 01:26:04
  29. Measure Formula Review 01:27:30
  30. Sorting Pivot Tables 01:29:39
  31. Total Revenue Per Store 01:30:07
  32. Adding Filters To Pivot Tables 01:32:53
  33. Questions 01:38:47
  34. Presentation Closing 01:39:59
  • Mike Thomas

    Mike Thomas

    Mike Thomas has worked in the IT training business for 26 years. His expertise and experience covers designing and delivering training courses, creating written training materials (Quick Reference Guides and step-by-step tutorials), recording and editing video-based tutorials and providing support t [...]

    View Full Profile

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 [email protected].

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.

  • .CSV 00:03:20, 00:05:45, 00:10:50, 00:44:15, 01:19:19
  • Analyze 00:12:01, 00:59:27
  • Cell Reference 00:20:17
  • Column 00:11:31, 00:13:07, 00:16:25, 00:21:14, 00:39:36, 01:01:12, 01:06:08, 01:20:28
  • Column Headings 00:14:33, 00:52:11, 01:07:46, 01:18:49
  • Data Model 00:02:21, 00:05:16, 00:06:01, 00:13:04, 00:27:00, 00:32:19, 00:39:23, 01:04:29, 01:16:59, 01:20:40
  • DAX - Data Analysis Expressions 00:01:23, 00:02:43, 00:20:29, 00:23:23, 01:01:03, 01:25:05
  • Filters 01:32:53
  • Formula 00:02:46, 00:31:41, 00:36:27, 00:50:41
  • Formula Bar 00:17:17, 00:20:04, 01:11:32, 01:24:44
  • IF Function 00:23:12, 00:31:11
  • LOOKUP Value 00:48:24
  • PDF 00:10:51
  • Pivot Table 00:03:57, 00:11:47, 00:26:16, 00:38:01, 00:42:24, 00:46:39, 00:59:18, 01:07:34, 01:19:33, 01:35:10
  • Power Pivot 00:00:08, 00:02:22, 00:06:16, 00:10:00, 00:13:10, 00:19:49, 01:01:04
  • Power Query 00:04:19
  • Query 00:32:39
  • Refresh 00:45:52, 01:19:05, 01:34:30
  • Row 01:05:46, 01:14:32
  • Slicer 00:46:57, 00:59:08
  • Spreadsheet 00:08:10, 00:10:33, 00:12:4
  • Table 00:16:46
  • VLOOKUP 00:48:10

.CSV: Comma-Separated Value files are text files where each field of data is separated by a comma. This is an effective means to export data from QuickBooks that you, in turn, wish to analyze in Excel.

Analyze: The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable.

Cell Reference: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are three types: Relative, Absolute, and Mixed

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.

Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.

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

Formula Bar: A toolbar at the top of the Microsoft Excel spreadsheet window that you can use to enter or copy an existing formula into cells or charts. It is labeled with function symbol (fx). By clicking the Formula Bar, or when you type an equal (=) symbol in a cell, the Formula Bar will activate.

IF Function: Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

LOOKUP: The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel.

PDF: Portable Document Format, a universal document format created by Adobe that allows cross-platform compatibility of documents.

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.

Refresh: The Refresh command appears on the Options tab of Excel 2007 and 2010 as well as the Analyze tab of Excel 2013. Pivot tables store a snapshot of the underlying source data, so they don’t immediately reflect changes to said data. You must periodically refresh any pivot table to ensure it reflects any changes to the source data.

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.

Slicer: You can insert slicers in Excel to quickly and easily filter pivot tables. Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click

Spreadsheet: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.

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.

VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.

Customer Satisfaction Guarantee
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

Frequently Asked Questions

Understanding the distinction between Calculated Columns and Measures is fundamental to effective Power Pivot use. A Calculated Column is a DAX formula added to a table in the Power Pivot data model that evaluates row-by-row, storing a computed value for every row—similar to adding a formula column in Excel. For example, a Profit column calculated as [Revenue] - [Cost] stores the profit for each individual record. Calculated Columns consume memory because they store values for every row. A Measure, by contrast, is a dynamic DAX calculation that computes its result based on the current filter context of a PivotTable—it has no fixed result until evaluated within a report. A Total Profit Measure might sum the Profit values visible given the current row, column, and slicer selections. Measures are more powerful and memory-efficient than calculated columns for aggregations. The key rule: use Calculated Columns for row-level attributes; use Measures for aggregations and KPIs that respond to PivotTable filters. Mike Thomas covers both in Aurora Training Advantage's Excel: Power Pivot For The Everyday Excel User webinar.
CALCULATE is considered the most important and powerful function in DAX, the formula language used in Power Pivot. Its syntax is CALCULATE(expression, filter1, filter2, ...). It evaluates a DAX expression while modifying the current filter context—allowing you to override, add, or remove filters that would normally be applied by the PivotTable. For example, =CALCULATE(SUM(Sales[Revenue]), Region="North") always returns North region revenue regardless of what region filter is active in the PivotTable. This ability to modify context is what makes advanced analytics possible in Power Pivot: calculating year-over-year comparisons, running totals, percentage-of-total across different slicing dimensions, and what-if scenarios. CALCULATE works in conjunction with filter functions like FILTER, ALL, ALLEXCEPT, and SAMEPERIODLASTYEAR to precisely control what data is included in a calculation. Virtually every sophisticated DAX Measure relies on CALCULATE at its core. Mike Thomas introduces the CALCULATE function in Aurora Training Advantage's Excel: Power Pivot For The Everyday Excel User webinar as part of a practical DAX curriculum.
CUBE functions are a set of Excel worksheet functions that query the Power Pivot Data Model directly from worksheet cells, allowing you to display model values anywhere in a spreadsheet without building a PivotTable. The most commonly used are CUBEMEMBER (returns a member or element from the data model) and CUBEVALUE (retrieves an aggregated value from the model). For example, =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total Revenue]", "[Calendar].[Year].[2024]") pulls the 2024 total revenue from the model into a specific cell. This allows you to build fixed-layout financial reports and dashboards that draw live data from the model without the PivotTable's dynamic layout constraints. CUBE functions power Excel's Analyze in Power BI feature and form the backbone of many professional reporting templates. They are automatically generated when you convert a PivotTable to formulas (PivotTable > Options > OLAP Tools > Convert to Formulas). Mike Thomas introduces CUBE functions in Aurora Training Advantage's Excel: Power Pivot For The Everyday Excel User webinar.
DAX provides the RELATED function as the primary lookup mechanism within the Power Pivot Data Model, replacing the need for VLOOKUP when working with related tables. RELATED traverses a relationship between tables and returns a value from the related table: in a Sales table, =RELATED(Products[Category]) retrieves the category for each product based on the relationship between the Sales and Products tables. This works because you've defined a relationship in the Data Model (similar to a database join) connecting the product key in both tables. For lookup scenarios where the relationship direction is reversed (one-to-many from the lookup side), RELATEDTABLE returns all matching rows, which can then be aggregated. For more complex lookups outside of defined relationships, LOOKUPVALUE provides a direct value retrieval: =LOOKUPVALUE(table[return_column], table[search_column], search_value) — functionally similar to VLOOKUP but working within DAX context. These functions together make the Data Model a self-contained analytical environment without needing formula columns in worksheets. Mike Thomas covers DAX lookup techniques in Aurora Training Advantage's Excel: Power Pivot For The Everyday Excel User webinar.
By default, PivotTable row labels sort alphabetically, but business reports almost always need rows sorted by a metric—showing the most profitable store first, the top-selling product at the top, or the highest-spending customer ranked first. In a Power Pivot-backed PivotTable, sorting by a measure is straightforward: click any cell in the values area of the PivotTable, then use the Sort Largest to Smallest button (Data tab or right-click context menu). The row labels immediately reorder based on the selected measure's values. For tables with a Slicer or filter applied, the sort respects the current filter context—so the ranked order updates dynamically as selections change. You can also sort by a column not currently visible in the PivotTable by adding the Sort By Column setting in the Power Pivot window (for Calculated Column sort orders) or by using the More Sort Options dialog within the PivotTable Field List. Sorting by value rather than alphabet transforms a data table into a performance ranking report with no additional formulas. Mike Thomas demonstrates this technique in Aurora Training Advantage's Excel: Power Pivot For The Everyday Excel User webinar.