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

Collie, Rob

Brossura

ISBN 10: 1615470158 ISBN 13: 9781615470150

Casa editrice: Holy Macro Books, 2012

Riassunto

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

Su questo libro

Informazioni sull?autore

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.

Estratto. © Ristampato con autorizzazione. Tutti i diritti riservati.

DAX Formulas for PowerPivot

By Rob Collie

Holy Macro! Books

Copyright © 2013 Robert Collie and Tickling Keys, Inc.
All rights reserved.

ISBN: 978-1-61547-015-0

CHAPTER 1

A Revolution Built on YOU

Does This Sound Familiar?

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 point 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, actually?"

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 she and her seatmate 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 on the verge 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.

Aside: Most Excel Pros do not think of themselves as Pros: I find that most are quite modest about their skills. However, take it from someone who has studied Excel usage in depth: if you fit the bulleted criteria above, you are an Excel Pro. Wear the badge proudly.

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 10% of all Excel users created PivotTables.

'Create' is an important word here — much more than 10% consume pivots made by others, but only 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.

I, too, fit the definition of an Excel Pro. It is no accident that I found myself drawn to the Excel team after a few years at Microsoft, and it is no accident that I ultimately left to start an Excel / PowerPivot-focused business (and blog). While I have been using the word 'you' to describe Excel Pros, I am just as comfortable with the word 'we.'

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 about 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!) relies heavily on Excel Pros to be his eyes and ears for all things financial. Yes, I am sure that now, many years later, he 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 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 the decision, or how large the organization — 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.

For more on Big Data, see http://ppvt.pro/SaavyBigData.

Ingredient Two: Economic Pressure

The world has been in an economic downturn since 2008 and there is little sign of that letting up. 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.

Insights are the key

Up to this point, I have used terms like 'crunched data,' 'reports,' 'Small Data,' and 'digestible information' to refer to the output produced by Excel Pros (and the BI industry). Ultimately though, the decision makers need insights — they need to learn things from the data that help them improve the business.

I like to use the word 'insights' to remind myself that we can't just crunch data blindly (and blandly) and hand it off. We need to keep in mind that our job is to deliver insights, and to create an environment in which others can quickly find their own. I encourage you to think of your job in this manner. It makes a real difference.

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: PowerPivot

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 already skilled analytical thinkers, 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 already 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.

(Continues...)

Excerpted from DAX Formulas for PowerPivot by Rob Collie. Copyright © 2013 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.

Dettagli dell?edizione

• Editore: Holy Macro Books
• Data di pubblicazione: 2012
• ISBN 10: 1615470158
• ISBN 13: 9781615470150
• Rilegatura: Copertina flessibile
• Lingua: Inglese
• Numero di pagine: 246
• Contatto del produttore: non disponibile