DAX Formulas for PowerPivot: The Excel Pro's Guide to Mastering DAX by Rob Collie, Avichal Singh |, Paperback | Barnes & Noble
DAX Formulas for PowerPivot: The Excel Pro's Guide to Mastering DAX

DAX Formulas for PowerPivot: The Excel Pro's Guide to Mastering DAX

3.6 3
by Rob Collie
     
 

View All Available Formats & Editions


Microsoft’s Power Pivot is an add-on to Excel that enables you to produce new kinds of reports and analyses that were simply impossible before. This book is the first to tackle DAX formulas, the core capability of Power Pivot, from the perspective of the Excel audience. Written by a leading Power Pivot educator (and former leader on the Power Pivot and Excel

Overview


Microsoft’s Power Pivot is an add-on to Excel that enables you to produce new kinds of reports and analyses that were simply impossible before. This book is the first to tackle DAX formulas, the core capability of Power Pivot, from the perspective of the Excel audience. Written by a leading Power Pivot educator (and former leader on the Power Pivot and Excel teams at Microsoft), the book’s concepts and approach are introduced in a simple, step-by-step manner tailored to the learning style of Excel users everywhere.

The techniques presented allow users to produce, in hours or even minutes, results that formerly would have taken entire teams weeks or months to produce. In this book you will learn how Power Pivot:

1) Gives you “portable” formulas that can be re-used across multiple different reports with a single click.
2) Removes the need to ever write a VLOOKUP formula again.
3) Allows you to add smart calculations to pivots, such as “Year over Year” and “Moving Averages” which auto-adjust as the pivot changes.
4) Effortlessly merges disjointed sets of data into unified insight.

As a bonus, Power Pivot and DAX formulas are both the heart AND brain of Microsoft’s “Power BI” system, giving us a long-needed bridge between the worlds of Excel and Business Intelligence – a bridge that any Excel PivotTable user can cross with the help of this easy-to-follow book. Your new career – and your organization’s future – starts within these pages

Product Details

ISBN-13:
9781615470150
Publisher:
Holy Macro! Books
Publication date:
01/15/2013
Edition description:
New Edition
Pages:
239
Product dimensions:
8.20(w) x 10.70(h) x 0.50(d)

Related Subjects

Read an Excerpt

Power Pivot and Power BI: the Excel User's Guide to the Data Revolution


By Rob Collie, Avi Singh

Holy Macro! Books

Copyright © 2016 Robert Collie and Tickling Keys, Inc.
All rights reserved.
ISBN: 978-1-61547-039-6



CHAPTER 1

A Revolution Built On YOU


Does This Sound Familiar?

(Updated Fall 2015, but we decided to leave this part in Rob's first-person "voice" – because the authenticity is better-preserved).

In the movie Fight Club, Edward Norton's character refers to the people he meets on airplanes as "single serving friends" – people he befriends for three hours and never sees again. I have a unique perspective on this phenomenon, thanks to a real-world example that is relevant to this book.

A woman takes her seat for a cross-country business flight and is pleased to see that her seatmate appears to be a reasonably normal fellow. They strike up a friendly conversation, and when he asks her what she does for a living, she gives the usual reply: "I'm a marketing analyst."

That answer satisfies 99% of her single-serving friends, at which the conversation typically turns to something else. However, this guy is the exception, and asks the dreaded follow-up question: "Oh, neat! What does that mean?"

She sighs, ever so slightly, because the honest answer to that question always bores people to death. Worse than that actually: it often makes the single-serving friend recoil a bit, and express a sentiment bordering on pity.

But she's a factual sort of person, so she gives a factual answer: "well, basically I work with Excel all day, making PivotTables." She fully expects this to be a setback in the conversation, a point on which we share no common ground.

Does this woman's story sound familiar? Do you occasionally find yourself in the same position?

Well imagine her surprise when this particular single-serving friend actually becomes excited after hearing her answer! He lights up – it's the highlight of his day to meet her.

Because, you see, on this flight, she sat down next to me. And I have some exciting news for people like her, which probably includes you


Excel Pros: The World Is Changing in Your Favor

If you are reading this, I can say confidently that the world is in the early stages of an incredible discovery: it is about to realize how immensely valuable YOU are. In large part, this book is aimed at helping you reap the full rewards available to you during this revolution.

That probably sounds pretty appealing, but why am I so comfortable making bold pronouncements about someone I have never met? Well, this is where the single-serving friend thing comes in: I have met many people like you over the years, and to me, you are very much 'my people.'

In fact, for many years while I worked at Microsoft, it was my job to meet people like you. I was an engineer on the Excel team, and I led a lot of the efforts to design new functionality for relatively advanced users.

Meeting those people, and watching them work, was crucial, so I traveled to find them. When I was looking for people to meet, the only criteria I applied was this: you had to use Excel for ten or more hours per week.

I found people like that (like you!) all over the world, in places ranging from massive banks in Europe to the back rooms of automobile dealerships in Portland, Oregon. There are also many of you working at Microsoft itself, working in various finance, accounting, and marketing roles, and I spent a lot of time with them as well (more on this later).

Over those years, I formed a 'profile' of these 'ten hour' spreadsheet people I met. Again, see if this sounds familiar.


Attributes of an Excel Pro:

• They grab data from one or more sources.
• They prep the data, often using VLOOKUP.
• They then create pivots over the prepared data.
• Sometimes they subsequently index into the resulting pivots, using formulas, to produce polished reports. Other times, the pivots themselves serve as the reports.
• They then share the reports with their colleagues, typically via email or by saving to a network drive.
• They spend at least half of their time re-creating the same reports, updated with the latest data, on a recurring basis.

At first, it seemed to be a coincidence that there was so much similarity in the people I was meeting. But over time it became clear that this was no accident. It started to seem more like a law of physics – an inevitable state of affairs. Much like the heat and pressure in the earth's crust seize the occasional pocket of carbon and transform it into a diamond, the demands of the modern world 'recruit' a certain kind of person and forge them into an Excel Pro.

I can even put an estimate on how many of you are out there. At Microsoft we used to estimate that there were 300 million users of Excel worldwide. This number was disputed, and might be too low, especially today. It's a good baseline, nothing more. But that was all users of Excel – from the most casual to the most expert. Our instrumentation data further showed us that only 5-10% of all Excel users created PivotTables.

'Create' is an important word here – many more than consume pivots made by others, but only 5-10% are able to create them from scratch. Creating pivots, then, turns out to be an overwhelmingly accurate indicator of whether someone is an Excel Pro. We might as well call them Pivot Pros.

You may feel quite alone at your particular workplace, because statistically speaking you are quite rare – less than 0.5% of the world's population has your skillset! But in absolute numbers you are far from alone in the world – in fact, you are one of approximately thirty million people. If Excel Pros had conferences or conventions, it would be quite a sight.

As I said up front, I am convinced that our importance is about to explode into the general consciousness. After all, we are already crucial.


Our Importance Today

As proof of how vital we are, here's another story from Microsoft, one that borders on legend. The actual event transpired more than ten years ago and the details are hazy, but ultimately it's about you; about us.

Someone from the SQL Server database team was meeting with Microsoft CEO Steve Ballmer. They were trying to get his support for a 'business intelligence' (BI) initiative within Microsoft – to make the company itself a testbed for some new BI products in development at that time. If Steve supported the project, the BI team would have a much easier time gaining traction within the accounting and finance divisions at Microsoft.

In those days, Microsoft had a bit of a 'prove it to me' culture. It was a common approach to 'play dumb' and say something like, "okay, tell me why this is valuable." Which is precisely the sort of thing Steve said to the BI folks that day.

To which they gave an example, by asking a question like this: "If we asked you how much sales of Microsoft Office grew in South America last year versus how much they grew the year before, but only during the holiday season, you probably wouldn't know."

Steve wasn't impressed. He said, "sure I would," triggering an uncomfortable silence. The BI team knew he lacked the tools to answer that question – they'd done their homework. Yet here was one of the richest and most powerful men in the world telling them they were wrong.

One of the senior BI folks eventually just asked straight out, "Okay, show us how you'd do that."

Steve snapped to his feet in the center of his office and started shouting. Three people hurried in, and he started waving his arms frantically and bellowing orders, conveying the challenge at hand and the information he needed. This all happened with an aura of familiarity – this was a common occurrence, a typical workflow for Steve and his team.

Those three people then vanished to produce the requested results. In Excel, of course.


Excel at the Core

Let that sink in: the CEO of the richest company in the world (and one of the most technologically advanced!) relied heavily on Excel Pros to be his eyes and ears for all things financial. Yes, I am sure that now, many years later, Satya Nadella has a broad array of sophisticated BI tools at his disposal. However, I am equally sure that his reliance on Excel Pros has not diminished by any significant amount.

Is there anything special about Microsoft in this regard? Absolutely not! This is true everywhere. No exceptions. Even at companies where they claimed to have 'moved beyond spreadsheets,' I was always told, off the record, that Excel still powered more than 90% of decisions. (Indeed, an executive at a large Microsoft competitor told me recently that his division, which produces a BI product marketed as a 'better' way to report numbers than Excel, uses Excel for all internal reporting!)

Today, if a decision – no matter how critical it is, or how large the organization is – is informed by data, it is overwhelmingly likely that the data is coming out of Excel. The data may be communicated in printed form, or PDF, or even via slide deck. But it was produced in Excel, and therefore by an Excel Pro.

The message is clear: today we are an indispensable component of the information age, and if we disappeared, the modern world would grind to a halt overnight. Yet our role in the world's development is just getting started.


Three Ingredients of Revolution

There are three distinct reasons why Excel Pros are poised to have a very good decade.


Ingredient One: Explosion of Data

The ever-expanding capacity of hardware, combined with the ever-expanding importance of the internet, has led to a truly astounding explosion in the amount of data collected, stored, and transmitted.

Estimates vary widely, but in a single day, the internet may transmit more than a thousand exabytes of data. That's 180 CD-ROMs' worth of data for each person on the planet, in just 24 hours!

However, it's not just the volume of data that is expanding; the number of sources is also expanding. Nearly every click you make on the internet is recorded (scary but true). Social media is now 'mined' for how frequently a certain product is mentioned, and whether it was mentioned positively or negatively. The thermostat in your home may be 'calling home' to the power company once a minute. GPS units in delivery vehicles are similarly checking in with 'home base.'

This explosion of volume and variety is often lumped together under the term 'Big Data.' A few savvy folks are frontrunning this wave of hype by labeling themselves as 'Big Data Professionals'. By the time you are done with this book, you might rightfully be tempted to do the same.

There's a very simple reason why 'Big Data' equals 'Big Opportunity' for Excel Pros: human beings can only understand a single page (at most) of information at a time. Think about it: even a few hundred rows of data is too big for a human being to look at and make a decision. We need to summarize that data – to 'crunch' it into a smaller number of rows (i.e. a report) – before we can digest it.

So 'big' just means 'too big for me to see all at once.' The world is producing Big Data, but humans still need Small Data. Whether it's a few hundred rows or a few billion, people need an Excel Pro to shrink it for human consumption. The need for you is only growing.


Ingredient Two: Economic Pressure

Much of the world has been in an economic downturn since 2008, and in general this is a bad thing. If played properly, however, it can be a benefit to the Excel Pro.

Consider, for a moment, the BI industry. BI essentially plays the same role as Excel: it delivers digestible information to decision makers. It's more formal, more centralized, and more expensive – an IT function rather than an Excel Pro function – but fills the same core need for actionable information.

A surprising fact: paradoxically, BI spending increases during recessions, when spending on virtually everything else is falling. This was true during the dot-com bust of 2000 and is true again today.

Why does this happen? Simply put: when the pressure is on, the value of smart decisions is increased, as is the cost of bad ones. I like to explain it this way: when money is falling from the sky, being 'smart' isn't all that valuable. At those times, the most valuable person is the one who can put the biggest bucket out the window. However when the easy money stops flowing, and everyone's margins get pressured, 'smart' becomes valuable once again.

Unlike BI spending, spending on spreadsheets is not measured – people buy Microsoft Office every few years no matter what, so we wouldn't notice a change in 'Excel spending' during recessions. I suspect, however, that if we could somehow monitor the number of hours spent in Excel worldwide, we would see a spike during recessions, for the same reason we see spikes in BI spending.

So the amount and variety of data that needs to be 'crunched' is exploding, and at the same time, the business value of insight is increasing. This is a potent mixture.

All it needs is a spark to ignite it. And boy, do we have a bright spark.


Ingredient Three: Dramatically Better Tools

The world's need for insights is reaching a peak. Simultaneously, the amount of data is exploding, providing massive new insight opportunities (raw material for producing insights). Where is the world going to turn?

It is going to take an army of highly skilled data professionals to navigate these waters. Not everyone is cut out for this job either – only people who like data are going to be good at it. They must also be trained already – there's no time to learn, because the insights are needed now!

I think you see where I am going. That army exists today, and it is all of YOU. You already enjoy data, you are well-versed in the nuances of your particular business, and you are already trained on the most flexible data analysis tool in the world.

However, until now there have been a few things holding you back:

1. You are very busy. Many of you are swamped today, and for good reason. Even a modestly complex Excel report can require hundreds of individual actions on the part of the author, and most of those actions need to be repeated when you receive new data or a slightly different request from your consumers. Our labor in Excel is truly "1% inspiration and 99% perspiration," to use Edison's famous words.

2. Integrating data from multiple sources is tedious. Excel may be quite flexible, but that does not mean it makes every task effortless. Making multiple sources 'play nicely' together in Excel can absorb huge chunks of your time.

3. Truly 'Big' Data does not fit in Excel. Even the expansion of sheet capacity to one million rows (in Excel 2007 and newer) does not address all of today's needs. In my work at Pivotstream I sometimes need to crunch data sets exceeding 100 million rows, and even data sets of 100,000 rows can become prohibitively slow in Excel, particularly when you are integrating them with other data sets.

4. Excel has an image problem. It simply does not receive an appropriate amount of respect. To the uninitiated, it looks a lot like Word and PowerPoint – an Office application that produces documents. Even though those same people could not begin to produce an effective report in Excel, and they rely critically on the insights it provides, they still only assign Excel Pros the same respect as someone who can write a nice letter in Word. That may be depressing, but it is sadly true.


The answer is here

The Power BI family of tools addresses all of those problems. I actually think it's fair to say that it completely wipes them away.

You are the army that the world needs. You just needed an upgrade to your toolset. Power Pivot and its close cousin Power BI provide that upgrade and then some. I would say that we probably needed a 50% upgrade to Excel, but what we got is more like a 500% upgrade; and that is not a number I throw around lightly.

I bet you are eager to see that new jet airplane. Let's take a tour.


(Continues...)

Excerpted from Power Pivot and Power BI: the Excel User's Guide to the Data Revolution by Rob Collie, Avi Singh. Copyright © 2016 Robert Collie and Tickling Keys, Inc.. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Meet the Author

A former Microsoft engineering leader, Rob Collie is an entrepreneur, author, and consultant who believes Excel is enjoying a renaissance in power and importance - a trend that provides massive opportunities for the millions of Excel practioners worldwide. His passion is empowering and the Excel Professional to seize those opportunities. When he's not training, consulting, or writing, Rob can be found in his laboratory, devising new tools and techniques for the Excel community. He also operates the world's leading PowerPivot website (PowerPivotPro.com) and occassionally sleeps.

Customer Reviews

Average Review:

Write a Review

and post it to your social network

     

Most Helpful Customer Reviews

See all customer reviews >