Final Poll on Book Subtitle

May 30, 2013

Thanks again everyone, I’ve whittled it down to two existing titles and one new one. 

Please vote for one of them below – I’ve got to submit to the art department tonight Smile

“Relationship may be needed?” But I already have a relationship! What’s going on?

May 30, 2013


“Well I’m not quite ready for a relationship right now Lois.”

A confusing error getting you down?

I got an email this morning from a friend who was running into this problem:

PowerPivot says "Relationship may be needed" - but you do have a relationship.  What's going on?

Are you getting this warning in the field list,
even though you DO have a relationship in place?

What gives?  Let’s return to our recent movies example.

To help you diagnose this problem, should you run into it, first let’s get a simple example ready.

Two tables of data in PowerPivot:  Movies, and Years.

Read the rest of this entry »

Book Subtitle Survey, Part Deux

May 30, 2013

DAX Formulas for PowerPivot:

<insert new subtitle here>

(BTW I have a real post still coming later today – this doesn’t count)

Based on yesterday’s feedback (thanks everyone!), here’s a revised poll on changing the subtitle of the book.

Note:  this time you may choose up to TWO.

Quick Poll: New Subtitle for the Book?

May 29, 2013

DAX Formulas for PowerPivot:

<insert new subtitle here>

***UPDATE:  Poll closed, please see part two here:  Followup Poll.

Last week I mentioned that I was going to rename the book now that the first print run is about to finish selling out.  The title was a mistake on my part – it sounds dry and techie, and the reviews all say otherwise.  A few people even called it a “page turner” and specifically mentioned that the title made them expect something much drier.

After thinking about it some more, I don’t think completely changing the title is a good idea.  I don’t want people who already have the book to get confused and buy it again – that would feel pretty sleazy.

But I WILL change the subtitle.

Help me pick a new one before it goes to the printer Smile.  You can pick up to three, and/or write in another suggestion.  Thanks folks!

Memorial Day Musings: The Four Team Theory

May 28, 2013


We Typically Thought of the Cold War as a Struggle Between Two Opposing Teams.
But I Now Think There Were Actually Four Opposing Teams.
(and Four Teams in Every International Struggle – Past and Present)

Continuing a holiday tradition of sorts, tonight I thought I’d share a long-simmering, Off Topic thought.  Our regularly scheduled program of Excel, PowerPivot, BI, etc. resumes Thursday.

Let’s start with a movie quote, shall we?

“Why was the gun not loaded???”


In “Elizabeth:  The Golden Age,” Spain sends an assassin to shoot Queen Elizabeth of England.
He fires, but the gun was deliberately loaded with powder only – no bullet.  Why?

Here’s one of those movies that my wife watches while I type away on my laptop and only halfway pay attention.  But boy, did this sequence get my attention.

Read the rest of this entry »

My New “Recording Studio”

May 23, 2013


The Official PowerPivotPro Sound Booth
(Yes, all Good Sound Booths Have Primus Posters)

Even though today’s post is the first time you’ve seen a video from me in a long time, I’ve actually been doing quite a bit of video recording lately – stuff that pays the bills while I spin up my new (unannounced) company, and stuff that supports the launch of said company.

Recording some guest modules for Chandoo’s PowerPivot training course is one example of this (I am finishing those today, so for those of you who enrolled, look to see those show up sometime in the next week).

As part of this process I realized that I needed a better setup for sound.  A home recording studio on the cheap. 

It wasn’t that expensive, so I thought I’d share what I got:

Read the rest of this entry »

Be Gone, Scary VLOOKUP! (Short Film By R Collie)

May 23, 2013

I believe that written posts are better than videos most of the time – written posts allow people to skim or read in depth – or surreptitiously read a blog post during a meeting without tipping everyone off that you’re not paying attention Smile Written is also better for search engines.

I’m not changing my mind about that.  But there are some things that you just can’t show in a written post.  This one falls under that heading.

The other thing different today is that rather than show off some clever or powerful technique, I’m “going retro” and focusing on something very fundamental about PowerPivot, and how PowerPivot actually makes Excel a less scary place, and not just a more powerful place.

That’s what this short video is about. Oh, and Star Wars. And my sister, who I recently discovered is a Pivot Pro but hadn’t told me.  That traitor!


Read the rest of this entry »

Open Letter to My Friends at Microsoft: The Power of Excel’s Internal Network Effect

May 21, 2013

image  image

Excel on the Left.  Other Data Tools on the Right.

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).

You can do damn near anything in Excel.  Calendar chart visualizations.  Music videosBeautiful artMore music videosRespiration wavelengthsChess gamesWord clouds.

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)

Read the rest of this entry »

Pareto Analysis the Easy Way

May 16, 2013


“Computer?  Tell me how many products account for the top half of all sales?”

The scene in Star Trek IV where Scotty picks up the mouse and tries to instruct the computer by speaking into it was, in my opinion, the lone bright spot in an otherwise lackluster movie.  We all feel the same way sometimes of course.  We just want to say “hey computer can you just do what I want without making me trick you into doing it?” 

(Related:  I had a professor once who liked to say “as computer scientists we are trained to communicate with the dumbest things in the world – computers – so you’d think we’d be able to communicate quite well with people.”  He also writes a science and politics blog and was one of my favorite professors.)

OK, so how do we build something like this in PowerPivot:


The Handful of Products Shown Account for 50% of All Sales – But How Did I Build This?

First, Don’t Make it Harder Than It Is!

Read the rest of this entry »

First print Run Almost Sold Out :)

May 14, 2013


There Are More than 18 Copies Left at the Warehouse.  But Not THAT Many More Smile

When I was down at MrExcel HQ With Chandoo this weekend, Bill and I decided to check how many copies of the book were left in inventory at the distributor.

We printed 3,000 copies back in November.  The distributor only had about 350 left at the warehouse as of Friday.

Neither of us expected the inventory to be that low.  You see, book sales data isn’t exactly a timely business – Bill and I just got paid in April for the books that sold in November.  And while Nielsen does provide a window into how many books sold this past week, they only capture part of the market.  The proportion out there “in the shadows” is unknown, and it’s significant.

So much like what we see in the night sky is light that left distant stars many years ago, our only reliable sales data lags reality by 4-5 months.

Good News Bad News

Hey good news – the book has been selling even better than we knew!  Bill tells me that most books never sell their entire first print run, so to be running out so quickly is a very good sign.

Hey bad news – we gotta scramble to print a second run before the supply runs out Smile

Read the rest of this entry »

Heading Down to Uniontown (MrExcel HQ)!

May 10, 2013



Chandoo:  “It’s 46 miles to Uniontown, we’ve got a full tank of gas, half a stack of Excel books, it’s overcast, and we’re wearing sunglasses.”

Rob:  “Hit it.”

Hitting the Road.  Holding a Mini-Summit.

Chandoo and I are jumping in the car this afternoon for a short journey down I-77 to a place MrExcel calls home.  Well, at least, that’s where Bill lives and works today.  He is, sadly, moving to Florida this summer.  (The second friend of mine to leave Ohio for Florida since I moved here – traitors! Smile)

This is going to be fun, having the three of us in one place like this.  But I’m a little bummed that we couldn’t get a good picture of us sitting IN the car.  There was too much glare on the windshield.

Otherwise, we were prepared to re-enact this classic scene:


Me, Chandoo, and Bill in One Place?  Serious Stuff?  Maybe.

When a bunch of Excel “heavies” get together like this for a day, you might think the gathering looks like this:


Sometimes, that’s true.  But they just as often turn out more like the barbecue scene from Mystery Men:


I’m interested to see which way today goes Smile

Guest Post: GeoFlow using Panama’s Census Data

May 9, 2013

imageIntro from Rob

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).

Read the rest of this entry »