Share Description Microsoft PowerPivot es un complemento gratuito para Excel de Microsoft que permite a los usuarios producir nuevos tipos de informes y anlisis que simplemente eran imposibles antes, y este libro es el primero en abordar formulas DAX, la capacidad central de PowerPivot, desde la perspectiva de la audiencia de Excel. Escrito por el principal blogger a nivel mundial y practicante de la herramienta, los conceptos y el enfoque del libro se introducen de una manera paso a paso sencilla adaptada al estilo de aprendizaje de los usuarios de Excel en todas partes. Las tecnicas que se presentan permiten a los usuarios producir, en horas o incluso minutos, resultados que anteriormente habran tenido equipos enteros semanas o meses para producir e incluir lecciones sobre la diferencia entre las columnas y medidas calculadas, como formulas pueden ser reutilizadas a traves de informes de totalmente diferentes formas , como combinar conjuntos disjuntos de datos en informes unificados, como hacer que ciertas columnas en un pivote se comporten como si el pivote estuviese filtrado mientras otras columnas no, y como crear clculos de tiempo inteligentes en las tablas dinmicas como "Ao a Ao" y "Medias Moviles" si utilizan un calendario estndar, fiscal o un calendario personalizado. Las tecnicas de "tipo-patron" y las mejores prcticas contenidas en este libro se han desarrollado y perfeccionado a lo largo de dos aos de entrenamiento en sitio con los usuarios de Excel de todo el mundo, y las lecciones clave de los seminarios que cuestan miles de dolares por da ya estn disponibles dentro de las pginas de esta fcil de seguir gua. Microsoft PowerPivot is a free add-on to Excel from Microsoft that allows users to produce new kinds of reports and analyses that were simply impossible before, and this book is the first to tackle DAX formulas, the core capability of PowerPivot, from the perspective of the Excel audience. 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 and include lessons on the difference between calculated columns and measures, how formulas can be reused across reports of completely different shapes, how to merge disjointed sets of data into unified reports, how to make certain columns in a pivot behave as if the pivot were filtered while other columns do not, and how to create time-intelligent calculations in pivot tables such as "Year over Year" and "Moving Averages" whether they use a standard, fiscal, or a complete custom calendar.
|Published (Last):||20 June 2013|
|PDF File Size:||4.56 Mb|
|ePub File Size:||9.99 Mb|
|Price:||Free* [*Free Regsitration Required]|
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. However, this guy is the exception, and asks the dreaded follow-up question: "Oh, neat!
What does that mean? Worse than that actually: it often makes the single-serving friend recoil a bit, and express a sentiment bordering on pity. 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! 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? 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! 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. Again, see if this sounds familiar. Other times, the pivots themselves serve as the reports. 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. I can even put an estimate on how many of you are out there. At Microsoft we used to estimate that there were million users of Excel worldwide. This number was disputed, and might be too low, especially today.
But that was all users of Excel — from the most casual to the most expert. 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. 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. If Steve supported the project, the BI team would have a much easier time gaining traction within the accounting and finance divisions at Microsoft. He said, "sure I would," triggering an uncomfortable silence. Yet here was one of the richest and most powerful men in the world telling them they were wrong.
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!
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. 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.
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. Nearly every click you make on the internet is recorded scary but true.
By the time you are done with this book, you might rightfully be tempted to do the same. Think about it: even a few hundred rows of data is too big for a human being to look at and make a decision.
The need for you is only growing. Ingredient Two: Economic Pressure Much of the world has been in an economic downturn since , 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. 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 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. At those times, the most valuable person is the one who can put the biggest bucket out the window.
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. This is a potent mixture. All it needs is a spark to ignite it. And boy, do we have a bright spark. 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.
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.
Integrating data from multiple sources is tedious. Excel may be quite flexible, but that does not mean it makes every task effortless. In my work at Pivotstream I sometimes need to crunch data sets exceeding million rows, and even data sets of , rows can become prohibitively slow in Excel, particularly when you are integrating them with other data sets.
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. 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 bet you are eager to see that new jet airplane. Excerpted by permission of Holy Macro! 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.
DAX Formulas for PowerPivot: The Excel Pro's Guide to Mastering DAX
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. However, this guy is the exception, and asks the dreaded follow-up question: "Oh, neat! What does that mean? Worse than that actually: it often makes the single-serving friend recoil a bit, and express a sentiment bordering on pity. Do you occasionally find yourself in the same position?
DAX Formulas for PowerPivot
He was famous for being a tough interviewer. He was tough in ways that went above and beyond the call of duty. In fact, on at least one occasion he made a candidate cry. Not a good thing. This particular story is about what happened when a particularly eccentric candidate crossed paths with that draconian interviewer. You were going to be asked difficult, specific questions and expected to answer them. This style of interview is much more common these days I think.
Product View Status
DAX Formulas for PowerPivot