skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile

Clint Pondering Where to Buy More Cigars


“The Rite Aids on one side of town, the Walgreens on the other, and me right in the middle.”

-The Shopper with No Name (and a Fistful of Dollars)


One of the coolest things about my new job at PivotStream is all of the cool new data sources I get to play with.  I thought I’d take a brief break from the Rank member posts and share a little bit from one of those sources.

Retailer Overlap Report

I just finished cooking up this report – click for full-size version.

All Retailer Intersections with PowerPivot

What does that show?

It shows the top 15 US retail chains by number of stores, and their competition against one another by ZIP code population.

For example:  take the first row, Albertsons.  Of all the people who live near an Albertsons, 65.9% of them also live close to a CVS, but only 4.7% live near a Rite-Aid.

Cool, huh?

Marketing versus competitors

Let’s say you are in charge of advertising and promotions for a large grocery or drugstore chain.  One of your primary jobs is to lure consumers into your stores as opposed to your competitors’.  Every day you receive a collection of all the specials, advertisements, etc. that your competitors are running in various regions of the country.

Naturally, you want to respond to their efforts.  But you can’t focus on them all equally, or you’d fall hopelessly behind.  So, which ones should you pay the most attention to?  This is the kind of thing that would help you.

Sliceable too!

OK, let’s say you work for Safeway, and you only run advertising for them in the South region.  How useful is the nationwide report above?  Hard to say.

For instance, nationwide, Walgreens competes with you for 47.3% of your customers, and Rite-Aid is second at 36.1%.  Does that hold up in the South?

One click and you have a brand new report:

South Region Only Retailer Intersections with PowerPivot

And look, Walgreens falls to 14.1%!  Rite Aid climbs to 52.5%…  but CVS now checks in at a whopping 73.5%!

So no, the national report would mislead you.  Same thing would be true if you ran all newspaper advertising nationally for Safeway, but you were ignoring an ad that CVS was running in the South.

Also Sliceable by Income Range!

Lastly, let’s say you work for ShopRite, and you control advertising in the South region.  Walgreens is running a monthlong special on Product X, and you happen to have the ability to run a better special on Product X…  but should you?

Well, you consult the report above, and it shows you that Walgreens only competes with you for 34.9% of your customers.  So you decide not to run a special on Product X, since that costs you a lot of money for not enough benefit.

But suppose you knew that Product X was primarily targeted at households making between $50K and $75K.  Slice by that income and you get:

South Region Only Specific Income Range Retailer Intersections with PowerPivot

Hey look, Walgreens competes with you for 61.6% of your customers in that income range. 

So that ad campaign might be targeted specifically at stealing business from ShopRite, for all you know 🙂

How did I do this?

It’s complicated, and once again pushed the bounds of what I know about DAX measures.  It starts out a lot like a Market Basket Analysis, which takes some work to explain, but then it takes another twist.

I’ll explain in subsequent posts 🙂


But the crucial thing to note here for now is that this amount of flexibility is impossible in Excel by itself.

I mean, if I wanted to build a report that showed overlap between any two retail stores, with particular variables like region and income level held constant, I can do it.  Takes awhile but I can build one.

But as soon as someone says something like “I want to see this report filtered down to a different region,” well, you’re doing surgery on your original report.  Sometimes that takes almost as long as it did the first time around.

And when you’re done, well, now you have TWO reports to maintain.  Fun fun!

That’s the coolest thing about this report for me:  it remains flexible.  Its consumers don’t have to ask me for new versions of the report.  They can just click to filter.

And if they want new variables added, like racial demographics, that’s just a matter of adding new calculated columns to the PowerPivot window, not a new report.  I might even do that for next time.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

Leave a Comment or Question