This course provides step-by-step instructions for how to set-up a business intelligence dashboard in Excel using the Power Pivot add-on. You’ll learn the key functions and best practices for setting-up a dashboard. As you progress through the course, you’ll apply everything you’ve learned to a hands-on example using data we provide. By the end of this course, you’ll walk away with everything you need to create your own dashboards in Excel—so you can analyze and present data, track KPIs, and make your data easily accessible.
Upon completing this course, you will be able to:
● Create a Power Pivot table by drawing data from several different tables
● Develop simple calculated metrics with consistent formatting to be re-used across our dashboard
● Assemble multiple pivot tables, charts, and dimensions into a dashboard
● Apply DAX formatting language to create measures and columns that draw data from other tables
● Manipulate context to calculate more advanced metrics
● Utilize DAX Advanced Time Intelligence to create powerful period comparisons and KPIs
Overview
Syllabus
- Course Orientation
- In this module, you'll explore how Power Pivot transforms Microsoft Excel, bringing business intelligence and analysis capabilities to the forefront. Discover the key benefits, from creating pivot tables with multiple sources to performing powerful time period comparisons, and elevate your analysis skills to the next level with simple formulas and advanced functionalities.
- Module 1: A Basic Data Model
- Dive into the world of data modeling as we guide you through the process of building a relational data model in Power Pivot. Unleash the power of new functionalities and learn to import, relate, summarize, and interact with your data using measures, tables, and slicers for enhanced Excel capabilities.
- Module 2: Basic Measures
- Welcome to the next chapter of our Power Pivot Mastery course! Now that you've mastered data consolidation and interaction, this chapter delves into the world of DAX measures. Learn how to create metrics, leverage DAX as a powerful formula language, and unlock new possibilities for data aggregation and analysis in your Excel data model.
- Module 3: Working with a Star Schema
- Welcome to our Advanced Power Pivot Techniques module! Building upon your understanding of the Power Pivot data model and DAX, explore how to efficiently aggregate and summarize data with reusable formulas. This chapter introduces the Star Schema data model, guiding you through expanding datasets, managing multiple dimension tables, and creating interactive dashboards with a logical flow. Learn the significance of the Star Schema, leverage timelines, pivot charts, and enhance data import efficiency by combining Power Query with Power Pivot for a cohesive and consistent dashboard experience.
- Module 4: From Number to Narratives
- In this module, explore the art of turning data into meaningful insights and compelling data stories. Learn to create advanced DAX measures, employ the CALCULATE function for percent of total calculations, delve into DAX Time Intelligence for year-over-year comparisons, and enhance dashboard aesthetics with conditional formatting, KPIs, and visual cues. Elevate your data presentation skills and gain a solid foundation for crafting professional-looking dashboards that communicate insights effectively.
- Module 5: Extended Practice
- Welcome to the end-of-course exercise! Apply your skills to the Big Wings Airline case study, where you'll independently create a competitor analysis dashboard. Utilize your data modeling, metrics creation, and dashboard formatting knowledge to meet the provided requirements and communicate key insights effectively. Happy practicing!
- Course Conclusion
- Congratulations on completing our Power Pivot course! You've mastered the art of building three insightful dashboards, creating data models, understanding DAX basics, and delving into time intelligence calculations.
Taught by
Sebastian Taylor and Joseph Yeates
