Learn Power Pivot for Excel
What is Power Pivot?
What is Power Pivot?
Lets take a closer look at the definition.
Connect: You can use multiple tables of data & set up relationships between them using Power Pivot. For example, you can connect customer details to sales transactions so that you can summarize sales by customer location or gender easily.
Analyze: You can create simple pivot table style reports or create something exceedingly complex by defining your own calculated fields for values area of the pivot. There is a whole set of formulas defined for exactly this purpose, called as DAX formulas.
Visualize: Instantly filter your reports using slicers, time lines (Excel 2013 or above only), conditional formats, pivot charts etc. You can even define KPIs and see the performance in bands.
Massive Amounts of Data: Although your typical Excel worksheet contains a million rows, if you tried to load even half of those with any data, Excel would quickly become slow & lazy. Power Pivot can take a million rows for breakfast and would be hungry for more. It can processes millions of rows of data very quickly and easily, all from the comfort of a standard desktop or laptop.
Excel Add-in: Power Pivot is an optional free add-in that works with Excel 2010 or above. If you are running Excel 2010, you need to download and install it. Users of Excel 2013 have it easy. Power Pivot comes pre-packaged with Excel, you just need to enable it.
A brief introduction of Power Pivot
It would not be an understatement to say Power Pivot is the most awesome data analysis feature you will ever come across in Microsoft Excel. Since you would not take something short of awesome for this introduction, I recorded a 25 minute video explaining what Power Pivot really is and how it works. Watch it below:
Introduction to DAX Measures
What is a measure?
Think of measure as a formula that would summarize the values for you.
Examples of measures
Although you may not have noticed it, you have already created a few measures in first lesson (and earlier in your life if you ever used regular pivot tables). Examples of measures are,
- Total sales
- Number of customers
- Count of purchases
- Total Quantity
- % of expenses over sales
As you can see, we have already used few of these measures in previous video. We used Total Sales & Total quantity.
So what is DAX then?
DAX stands for Data Analysis Expressions. It is a special language we need to use to create measures. Worry not, although it is special, it looks exactly like Excel formulas you have been using & writing all along.
DAX functions (or formulas) is a special set of functions that are part of Power Pivot and help you create measures for almost anything.
For example, below DAX formula is used to calculate the total sales amount.
=SUM(sales[sales amount])
Now that looks very simple… right!
DAX measures & how to create them?
The world of DAX is vast & exciting. Once you realize its potential, you would be jumping with joy. A cleverly crafted DAX measure can answer tricky management questions with ease (and save a ton of time in reporting).
Watch below video to understand the basics of DAX & learn several simple DAX formulas.
Downloads
Detailed Lesson Plans
Join our Power Pivot Class
Thank you so much for your interest in our Power Pivot program. I am super excited about this course and eagerly working on the content to make it awesome.
What is this course?
Who should go for this course?
This course is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.
Please note that you should be familiar with Excel & Pivot Tables and running at least Excel 2010 to enjoy this course.
More details about the course – Coming Soon
I am working on finalizing the course brochure, lesson plans and video examples. We will be updating this page and announcing Power Pivot class for enrollment very soon. Stay tuned.
In case you have not signed-up, please join our Power Pivot course waiting list. I will email you once we are ready for enrollment.