As part of the microsoft.com/learning team we release products throughout the year –courseware, books, exams and eLearning (check out MVA for tons of free courses). We often need to understand how our products perform after launch and how they compare against each other (in the first month since launch, first quarter, first year etc.). For Example: we would compare the various courses we launched around Visual Studio 2010. We may compare Visual Studio 2010 against Visual Studio 2012 courses. We may even compare Visual Studio against SQL Server. Or compare adoption by geography or customer segments.
I can imagine similar need for other businesses, e.g. a car manufacturer who needs to compare performance of various year, make and models.
Power Pivot and Power View can allow us to go from View 1 below, which is inscrutable at best, to View 2 which really helps us understand and differentiate the adoption ramp of various products. In this article, I would explain how you can go from View 1 to View 2 using the car manufacturer example.
View 1: Monthly Sales by Car Model Typical view available in BI, but not very insightful
View 2: Cumulative Sales since Launch, by Car Model Clear view into adoption ramp of various products
Intro from Rob: Never fear, last week’s series is still slated for completion, and in a special way. Watch this space on Thursday for some fireworks. For now, please enjoy Avi’s thoughts on the new forecasting component of Power View / Power BI.
PASS Business Analytics conference saw the announcement of a pretty cool Power View feature: Forecasting. I felt lucky to have been there and also to have had the opportunity to attend both of Rob Collie’s sessions (Data Revolution, Industrial Strength Excel). The Data Revolution session, I must say, was unlike anything I expected. No DAX formulas, no bullet points; just a path to data nirvana
The Power View forecasting feature was cool enough that I just had to play with it! I wanted to try it out with a few real world data sets. I ended up using Climate Data and Stock Market performance.
- First a quick look at the Power View forecasting functionality - Then I show you how I built the files using Power Query (The more I use that tool the more I like it)
You can find the link to the finished Excel file here. You can also watch me walk through the whole process in the video below:
Video Walkthrough: Forecasting in Power View and Power BI
Power View Forecasting in a Nutshell
In the ‘cloud first’ spirit that Microsoft has been following, the forecasting feature is only available in the online Power BI site (See microsoft.com/powerbi for more and to sign up for a free trial). To enable the forecasting feature, after opening your file on the Power BI site, you need to switch to the HTML5 mode by clicking on the icon at bottom right.
Click on this icon to enable the HTML5 mode with forecasting functionality
Power Pivot is the Engine that Turns Data Into Information! But We Can’t Understand This Properly Without Examining the Three Big Lies of Data
Goal: Answer Four Frequently-Asked Questions
So many things to say this week. Let’s jump in. Here are the questions I ultimately aim to answer, which are questions I get basically everywhere I go:
How do all of the Power BI Components relate to each other? Power Pivot, Power Query, Power View, Power Map, Q and A, etc. = Power Confusion for some folks. I get it.
Has Power Pivot become less important, now that we have all of these other new “Power *” tools?
Which tool should I learn first in the Power BI family?
Should I consider abandoning this stuff altogether in favor of <hot new technology X>? Tableau, Hadoop, R, etc.
In order to answer these, first we must confront some insidious lies that we are told every day.
Examining: The Three Big Lies of Data
The world of data, today, is clouded by Three Big Lies. These lies originate with all of the tools vendors – Oracle, IBM, Tableau, etc., and yes, Microsoft too is very much playing along.
Even though the Vendors are the Purveyors of these lies,they are NOT “at fault” for them. Because the world actually WANTS to be told these lies. BADLY wants to be told them, in fact. And because the audience is so receptive to these lies, the vendors naturally learn to tell them, and tell them well.
Vendors who DON’T learn to tell these lies? Well, those vendors don’t win many customers. And then those vendors disappear.
So while the lies COME from the vendors, the PROBLEM, really, is with US – the people who BUY the tools.
***BONUS:In addition to getting your name printed in the book, ALL pre-orders from MrExcel.com will include IMMEDIATE access to the “rough cuts” version of Alchemy in PDF form. Think of this as the 99% complete version of the book, a “final beta” of sorts. You can start reading next week, and then receive the final version when it’s ready in a few weeks. (Immediate access to the PDF is included with pre-orders of the physical book OR eBook).
About 160 People Got Their Names Printed in the First Book, and Seemed to Really Enjoy It. Time to Do That Again for My Long-Delayed New Book, Alchemy.
The long tug of war draws to a close…
Yes folks, it’s basically done. For over a year now, Bill and I have taken turns playing the roles of “Busy Guy Who Keeps Putting it Off” and “Impatient Guy Who Wonders Why the Other Guy Keeps Dragging His Feet.”
For the record, it looks like the game is ending with me holding the hot potato. Bill will forever remind me that I was the last hold up, I know this.
Order Tuesday April 1st Between 12 and 1 PM US Eastern Time
Pre-order the book on MrExcel.com during that 1-hour window and we will include your name in the book before it goes to the printer! (Yes we still have a narrow window for changes).
You read about my Power Pivot journey in my first blog post and in my subsequent blog post I elaborated on migration to Analysis Services Tabular Model (SSAS Tabular). I realize now though, that I did things out of order and need to address that in some way. As my journey outlined, before we switched over to SSAS Tabular, we moved our Power Pivot workbooks to SharePoint and started using Power View Reports. And Power View has been a key element of our success. For this post I’ll go back to the future and speak about
- Our success with Power View
- All the settings in Power Pivot related to Power View
p.s.: When I refer to Power View I am referring to Power View on SharePoint. I am not referring to Power View functionality built in to Excel 2013, since that is a fairly different experience than Power View on SharePoint.
Power View Success Story
I love Power View, except when I don’t. It can feel limiting at times and frustrating, especially to an excel user (which is all of us ). After demonstrating a really slick Power View report with all the bells and whistles (check out a sample from Microsoft BI at Power View Demo. Mine don’t look as good as this), the first question I often get from the user is, “Great, now how can I export this to Excel?” And my answer is – you can’t “Export to Excel is the third most common button in data/BI apps…after Ok and Cancel” (click for a real fun post!), and Power View does not have it. Yet! If the powers that be are reading, I think it’s feasible that an icon appears when you hover over Power View report elements, to export the underlying data in excel in a simple table format. Please consider that for the next release. Now that I am in begging mode might as well ask for – ability to re-label measures/column names in Power View Report and show numbers as Percentage of Total (like in Excel Pivots). The latter is doable using DAX but not easily so.
First: Understand that PowerPivot is Kinda Two Things
Let’s rewind all the way back to Office 2010, a world in which PV does not exist. (For most of you, we call this time Today. And for those stuck on 2007 or 2003, you may refer to this as Tomorrow. Or maybe even the Day After Tomorrow).
In that world, which is where this blog largely lives, it’s helpful to reflect that PowerPivot has two parts: the PowerPivot window, and the Excel window. They have the following relationship:
Today I’m going to “get my nerd on” in a big way. Buckle up.
The genesis of this post is an email I’ve been meaning to send to my contacts at Microsoft – one I’ve been thinking about writing for at least a year. But I also figure it’s the sort of thing you folks might find interesting, and I really don’t have time these days to write the same “opus” twice, so here goes – two birds with one stone. And it’s a friendly stone.
Has there ever been a tool as flexible as Excel?
Let’s take a moment and just marvel at Excel’s “range.” (VBA macro programmers – yes the joke is intentional).
But those are just the outliers really – the novelties. The truly valuable examples are much less dramatic and happen hundreds of thousands of times every day. I’ll give some examples in the next section.
Feature A Was NEVER “Intended” to Be Used With Feature B!
“Hey You Got Your Slicer in My Conditional Formatting!” (And then the whole jar fell into a bucket of DAX)
Very busy week for me. Fortunately Miguel is at the ready with another guest post.
It occurs to me that we’ve now had guest posts from the US, the UK, Holland, Canada, and Panama. That’s pretty cool.
Miguel told me that I was going to LOVE the first picture in this post. And I do. But I must say that, once I saw the title, I expected something along the lines of the picture at the right. The picture below is better.
OK, over to Miguel…
Figure shows the population distribution for Panama in 2010 Made Entirely in Excel!
If you read my latest guest post at Powerpivotpro’s blog, you’ll know that I’m working on a personal project trying to get a more visual aproach of the latest census Data for the country of Panama (where I’m from and I currently live in).
Hi folks, welcome back Over the holidays, Kasper submitted a post – yes, THAT Kasper. Appropriately for the year 2013, it is focused on Excel 2013. Not many people have 2013 on their desktops yet (even me really – I just have one “test” laptop running it), but over the course of this year I’ll be slowing “rotating” Excel 2013 topics onto the blog.
Anyway, Kasper and I decided to “hold” his post until today so that everyone sees it.
Over to Kasper…
“I’ll be back” – Kasper de Jonge
Ok its been a while since I blogged an actual scenario here on PowerPivotPro but here is another one . Its that time at the end of the year and folks here at Microsoft are out enjoying their vacation so lots of meetings get cancelled, this gives me the opportunity to do one of the the things I love, helping users of our products get the solution they need and write some blog posts :).
A few days ago I met a internal user who had 3 million rows of occurrences, products and dates in a SQL database and wanted to get some insights out of it, preferably in a highly visual output. We are fortunate here at Microsoft that we always get to play with the latest bits, so we have access to Excel 2013 that includes Power View.
In this blog post we will look at how we can show a top 10 list of best selling products in Power View and how we can solve a long tail problem that will allow us to visualize only the top best selling products in a chart and ignore the rest. I know these things are pretty straightforward in Excel (if you know where to find it) but it needs the help of DAX in Power View.
***UPDATE – FULL: Wave one filled up fast, no need for 48 hours. We actually went over 100 during the night and hit about 130. We’re letting all of those in, but are taking down the signup form now.
Stay tuned for news about Wave Two
If Ebenezer Scrooge Were Alive Today, He’d Use PowerPivot. And He’d Love This Post. (Believe it or not this is an original image I commissioned ten years ago)
Taking my “High Priest” Role VERY Seriously
There are a few themes that I just keep hammering on, month after month. Most of those revolve around the stunning new future opening up for Excel Pros. I believe every thing I say about that stuff. It is REAL.
One of the biggest and most transformational changes is this: taking your workbooks to the web. Workbooks were being emailed around back when Roxette topped the worldwide music charts. PowerPivot workbooks published to a server are a very, VERY different experience, one that inspires MUCH more respect from the person consuming them.
Short Version: Free Forever for Lighter Workloads
This week, Pivotstream is launching something that I’ve been dreaming about for a long time: a way for you (yes YOU, dear reader) to harness the power of PowerPivot server (publishing workbooks as interactive web apps)… for free.
Not a trial. This is more of a Dropbox-style model where lighter usage is completely free, and you only pay if you want more capacity. I want to remove any barriers I can so that you can experience what I’m talking about (without bankrupting my company of course), and I think we’ve figured out how to do that. But before I get into details, let me show you something.
Just Add Upload
Thanks to a recent focus group I recruited here on the blog, I learned that many people expect there to be some sort of intensive conversion process – it seems like you would need to put a lot of work into a workbook before it becomes an interactive web application like the ones on our demo site.
So I recorded a video showing that it’s much, MUCH simpler than that. Just upload
Upload and Share – Short Video Illustrating an Even Shorter Process (I recommend watching in HD and Fullscreen)
Benefits to Excel Pro
I didn’t have time to cover this in such a short video, so here’s a quick table comparing the old way to the new way, through the eyes of you, the Excel Pro: