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


“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

Memorial Day Musings: The Four Team Theory


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

My New “Recording Studio”


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

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

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

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

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

Pareto Analysis the Easy Way


“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

First print Run Almost Sold Out :)


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
  • 1
  • 2