Experiments in Linear Regression, Part 2

June 19, 2014

 
<< Back to Part 1

Download this workbook Here

Slope of a Linear Regression Line - Now We Just Need to Translate That into Power Pivot

The Formula for the Slope of a Linear Regression Line.  It’s Greek to Me.
(Get it?  Greek?  Sigh.  Anyway, click the image to view the article on StatisticsHowTo.com)

In Case of Emergency, Call JT Statmaster!

imageI struggle mightily to understand formulas expressed as Greek symbols.  I don’t know why really.  Probably because it seems so abstract – that notation sacrifices “humanity” in order to achieve precision and uniformity.  I get that, but it doesn’t make it easy for me.

Fortunately, we have people like JT Joyner.  JT was a student in one of my classes late last year.  And I dare say he was one of those “star pupils.”  He took to Power Pivot like a natural.  So natural, in fact, that a couple days after the class, he emailed me a Linear Regression workbook example, implemented in Power Pivot measures.  He translated Greek into, you know, formulas.

Six months later, yeah, I am just getting around to doing something with it.  But this is exciting stuff for sure.  I’m pumped.  Let’s dig in.

Read the rest of this entry »


Experiments in Linear Regression Land, Part 1

June 18, 2014

 
Something About This Reminded Me of Fraser Crane Running With Scissors

Something About This Reminds Me of Fraser Crane Running With Scissors
(Click for YouTube)

Stand Back…

Remember, I am NOT a statistician.  I slept through Statistics in college – and I mean in my dorm room bed, not even in the lecture hall.

I tend to learn via doing, and via teaching, so today’s post is a “forcing function” by which I attempt to stretch my brain and skill-set.

There’s an excellent chance, therefore, that I will do something wrong here.  In fact I’m hoping many of you more stats-minded people will chime in here and correct/extend this where appropriate.

What the Heck IS a Linear Regression, Anyway?

Linear Trendline in Excel

A linear trendline in an Excel chart is an example of linear regression

Read the rest of this entry »


Why Steven Levitt is Too Expensive

June 12, 2014

 
Me with personal hero Steven Levitt at last year's PASS BA conference.

“And therefore Steve, this clearly indicates that you are entirely too expensive.”

Picking on a Hero?  Sacrilege!

I had a particularly energizing “remote assist” with a client today (on GoToMeeting).  Just fantastic.  I was smiling for 90 straight minutes at the pure awesome that the two of us were conjuring out of the data.

The lightning bolt realization that struck me, shortly after that meeting, was that a hero of mine might be in trouble.  (Well, only a little bit, but still – a little bit!)

Rumor is that Freakonomics author Steven Levitt charges something in the neighborhood of $30k to show up and give a 1-hour talk.  He’s the best keynote speaker I have EVER seen/heard, hands down, so I think he’s worth that, and I do NOT think he’s in trouble there.

I don’t think his book business is in trouble, either.  Again, fantastic books.  Love ‘em.  Untouchable.

But his consulting business…  hmmm…  yeah, I think Power Pivot professionals may have something to say about that heh heh.

Today’s (Redacted) Example – Cannibalization?

Read the rest of this entry »


Manipulating Relationships in VBA (in 2013)

June 10, 2014

Guest Post From Dany Hoter

Intro from Rob:  Ah, the international man of mystery returns!  My first instincts when I think of Dany Hoter, other than “one of the most fabulous humans I have ever known,” generally can be summarized as “MDX and Cube Formulas Monster.”

But he’s far from a one-trick pony.  Generally speaking, he has a level of tenacity and patience rarely encountered outside of laboratory conditions.  Couple that with an insatiable drive for The Right Thing, and you get some crazy results.

Today is one such CRAZY example.  Simultaneously, he shows us how to compensate for a drillthrough bug, AND delivers a working example of relationship manipulation via VBA macros.

THIS IS AN ADVANCED TOPIC POST.  Feel free to skip this one.  This is the deep end of the pool and even I don’t swim in these particular waters yet.

Note of course that this technique is 2013 only, and will not work with Power Pivot in 2010.

Take it away, Dany…

A Drillthrough Bug with Inactive Relationships

I started this VBA project after one of our partners wrote to me about a customer complaint regarding inactive relationships in Power Pivot:

image

The Sales Table has TWO Relationships to Calendar – One is Based on OrderDate (Active), and the Other is Based on ShipDate (Inactive – Dashed Line)

 

Read the rest of this entry »


Avoid Constant Refreshes in Excel 2013

June 5, 2014

Guest Post by Scott at Tiny Lizard

Power Pivot 2013 is a bit, shall we say, aggressive with its desire to keep your reports up-to-date.

Renaming a measure that isn’t even used yet?
Please wait, while I refresh your reports.

Adding a brand new measure that can’t possibly be used yet? 
Please wait, while I refresh your reports.

You tilted your head to the side?
Please wait, while I refresh your reports.

I had a customer with 16 pivot tables per sheet, and about 16 sheets.  Making edits was getting very non-fun, so I finally took the time to look into this fantastically stupendously wonderfully awesome solution to the problem.Untitled_thumb[4]

It worked ok. Nyah-Nyah New entries get added to your pivot table context menu, as seen on right.  Simply disable when you intend to make a bunch of edits at once, and use the extra time to read Rob’s new book.

Highly recommended for those annoyed by this in 2013.


July 30-31 Cleveland Class: Now With Special Guest!

June 3, 2014

 
image

Yeah, the Crazy Aussie Himself Will Be Attending:
Former Coca-Cola BI Director, Matt Allington!

July 30-31 – Cleveland, Ohio. 

Conquer Data AND Meet Living Proof of Legitimacy.

Last time I taught one of these, I was flattered that half the class came in from out of state – Indiana and Pennsylvania, I believe.

Well, the famous Matt Allington, who left his BI Director Job at Coca-Cola to join the Power Pivot Revolution, is taking that to the next level, and flying in from Australia.

Class Info

Two full days, $1249 per student. 

  1. Download the PDF agenda HERE.
  2. Reserve your spot HERE.

Convince Your Boss?

We hear this a lot:  “I’d love to attend, just not sure how to sell it to the boss man/lady.”

You could always say, “well, a BI Director at Coke has bet his career on it, for $1249 we can have that power too.”

Failing that, drop us a note and we’ll help you with a tailored value proposition: 

empower@powerpivotpro.com


The Server I Always Wanted

June 2, 2014

 
This weekend, Chris (of ShakerGeek fame) completed the transfer of PowerPivotPro.com over to a new hosting provider.

Please be on the lookout for any glitches related to the transfer.  I don’t see anything out of place, but it’s a big site and we may have missed something.

This will be the fourth “home” in the site’s history (WordPress.com, Dreamhost, Bluehost, and now WiredTree, for those who are interested).

It provides us with several benefits that are exciting for a nerd like me:

  1. Faster Page Loads – We paid for the deluxe options whenever they were offered.  Solid State Drives for rapid retrieval of our 681 (and counting) posts.  Plus an upgrade called “LiteSpeed” which I didn’t even bother to understand, cuz, you know, it’s Light Speed.
  2. Restored Reliability – you may have noticed that the site has experienced some outages recently (averaging one a week for the past couple months).  That was, and is, unacceptable.  Bluehost, you have failed us for the last time.  You are in command now, Admiral Wired Tree.
  3. Platform for Overhaul – PowerPivotPro.com is about to get a makeover.  Widescreen format, cleaner visuals, mobile-friendly.  You know, modern.  (It’s crazy, in 2009 when we launched, the current theme WAS modern.  But five years go by pretty fast).

image

You Are in Command Now, Admiral WiredTree


Simple, Amazing, and Happy: A Story About Data

May 29, 2014

Last week’s two-part series on The Three Big Lies and The World Doesn’t Know Its Own Numbers got me thinking about how all of this is connected.  It’s all one big story, and I need to stitch it together.

I actually told a big part of that story in one of my sessions at the PASS BA CON earlier this month, and people seemed to genuinely both enjoy and benefit from it.  Because of that (and because I spent over 100 hours prepping that talk!), I’ve been meaning to re-record that talk for YouTube.

So I thought, hey, let’s cross the streams.  I’ll make a series of short videos that tell the entire story – including, but not limited to, what I shared at PASS.

Today I present the Introduction to the story.  Anyone who’s used Power Pivot for awhile will “recognize” the transformation I’m hinting at in this Intro, but if you’re just starting down the road, I hope you find it exciting.  Because you have some amazing discoveries just around the corner.

Please tell me what you think of this video – either here or on YouTube – because I’ll factor that into the series.  The more I know, the better I can tell the story.

(Note that we will be switching PowerPivotPro.com over to a new server this weekend, so comments might be disabled here starting Friday night).


Forecasting in Power View and Power BI

May 27, 2014

Guest Post by Avichal Singh

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 Smile

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.

clip_image001

Click on this icon to enable the HTML5 mode with forecasting functionality

Power View Forecasting in a Nutshell

Read the rest of this entry »


The World Doesn’t Know Its Own Numbers

May 22, 2014

 
Picking up from Tuesday’s post about the Three Big Lies in Data

I give you…  the World’s Most Honest Scorecard!  

image

In Moments of Honest Reflection, Most Organizations Would Acknowledge that
this Describes their Relationship with Data

The Most Important Numbers Generally Don’t Exist

image

The two graphics above kinda “steal the thunder” of this point, but I’ll elaborate anyway.  One of the grandest surprises for me, after leaving Microsoft, is just how blind the world is to its own data.

Read the rest of this entry »


The 3 Big Lies of Data. (And Power Pivot vs. Power View, Power Query, Q&A, etc.)

May 20, 2014

 
How Power Pivot, Power View, and the Other Power BI Tools Relate to Each Other

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:

  1. 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.
  2. Has Power Pivot become less important, now that we have all of these other new “Power *” tools?
  3. Which tool should I learn first in the Power BI family?
  4. 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

We want data tools vendors to lie to usThe 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.

Read the rest of this entry »


Thank You Rebecca Hellyer!

May 19, 2014

Headshot Ninja

Apologies for the brief off-topic post, but when someone does something excellent, I like to recognize it:

imageimage

image

Headshots of the PowerPivotPro Team (Cleveland Branch)

Changes Afoot!

We’re in the midst of making a lot of changes behind the scenes here at PowerPivotPro.  We’re changing website hosts again to avoid future outages, stepping up to a much faster server, redesigning the website…  and oh yeah, we’re making plans to expand the team (slowly – but surely).

Along those lines it was time for updated head shots that weren’t taken 6 years ago.  We had these taken on a recent trip to Chicago.  Rebecca Hellyer makes this all look SO easy – which takes a blend of “tech” chops and people skills.  If this were Power Pivot, she’d be nesting CROSSJOIN’s inside of FILTER’s while carrying on polite conversation.

So if you have photography needs in the Chicago area, definitely look her up.

Never fear, true Power Pivot content resumes tomorrow (Tuesday).