TopN as viewed by DAX Table Queries

March 13, 2014

Guest post by Scott Senkeresty


Get it?  “Median?”  SO Funny!


Rob is on-site with a client this week, so the reins, mic, baton or other appropriate metaphor gets handed to me today.  We get to today’s topic by means of a discussion on calculating a median in DAX:

Scott:  That sounds easy.  Just use TopN to grab half the numbers in ascending order, then another TopN against descending values to grab the final value(s)  (Glossing over odd vs even number of data rows)
Rob: What are you going to do about ties?
Scott:  <Blank Stare>

It turns out that calculating a median in DAX is pretty tricky.  Rob contends that sneaky street fighting tactics are required to deal with ties…where in my heart of hearts, I believe an elegant solution exists.  Hopefully we can get to the bottom of that in a future blog post.

Read the rest of this entry »

The Dramatic Impact of the 2013 SKU Decisions

November 21, 2013

Impact of the Power Pivot not included in all versions of Office 2013 decision, visualized dramatically.

This is a Pretty Dramatic Dip and Recovery.  What Else Could Explain It?  I’m all ears.

No DAX Required

Nothing fancy here, just a chart of page views of a very specific page on this site – the What is PowerPivot? page.  I use that page as an indicator of new interest – most people viewing it are “early” in their Power Pivot journeys. 

That dip starting in February is incredible.  What else could explain it other than the decision to remove Power Pivot from most “flavors” of 2013?  Eight months later, after Power Pivot re-appeared in Excel 2013 Standalone, we’re back to the same “slope” of the line that we had in February.  Maybe a little better.

Of course, Power Pivot usage was STILL growing, a LOT, during the dip.  And in fact growing by a lot more than the same months in 2012.  It’s just that the RATE of growth fell during those months.  The faucet of new users was still flowing, and flowing fast.  It was just “turned down” from full speed for a few months.

But imagine where we’d be WITHOUT this 6-8 month dip in growth rate.  The curve leading up to February looks awfully exponential doesn’t it?  Let’s take a look…

Read the rest of this entry »

Update on Power Pivot 2013 Outside the USA

October 15, 2013

OK, the haze is clearing a bit.  The news is mostly good but this whole situation never should have existed anyway.

1) People outside the USA *are* succeeding with Excel 2013 Standalone – it IS giving them Power Pivot, although in some cases it is requiring a reinstall before it shows up.

2) Amazon USA won’t sell to non-US customers.  OK, no biggie, if you’re outside the USA, you can get it direct from MS (link below).

US Customers can get Excel 2013 (and therefore Power Pivot) Here

Non-US Customers can get Excel 2013 (and Power Pivot) Here

(Yeah that non-US link just goes to the MS Store homepage because every
direct link to the product was country-specific.  Super helpful huh?)

3) After a successful install, you probably still have to “enable” the Power Pivot addin.  Instructions below.

After Installing Excel 2013 Standalone, You Still Have to Enable the Power Pivot Addin.  Step 1.

After Installing Excel 2013 Standalone, You Still Have to Enable the Power Pivot Addin.
Step 1.

After Installing Excel 2013 Standalone, You Still Have to Enable the Power Pivot Addin.  Steps 2-4.

After Installing Excel 2013 Standalone, You Still Have to Enable the Power Pivot Addin.
Steps 2-4.

After Installing Excel 2013 Standalone, You Still Have to Enable the Power Pivot Addin.  Steps 5-7.

After Installing Excel 2013 Standalone, You Still Have to Enable the Power Pivot Addin.
Steps 5-7.  Now You Should be Set.

Please report on your success stories and/or struggles in the comments.  Given what I’ve heard so far from readers, there should be a lot more success than failure.

Verified: Yes, Excel 2013 Standalone Now Includes Power Pivot. (For real, normal people have it.)

October 11, 2013


If You Install Excel 2013 Standalone, and Update to Version 1511, You Will Have Power Pivot

I got confirmation yesterday, both from Microsoft and from a reader, that this week’s patch update from Microsoft fixed the problem that’s been lingering for about six weeks.

1) So, if you have Office 2013, but it’s one of the versions that lacks Power Pivot, you can buy a downloadable version of Excel 2013 Standalone (available here from Amazon), install it, and Excel will now have Power Pivot.

2) If you’ve already got 2013 Standalone, just make sure you update to 1511, which should happen automatically anyway.

3) Or maybe you don’t have Office 2013 at all, but want to run Excel 2013 Power Pivot side-by-side with Excel 2010 PowerPivot (I do this on all of my computers, for the record), then again, just get the Excel 2013 Standalone download and you should be fine.

For history on this saga, see here and here.

***UPDATE: Excel 2013 Standalone Will NOT Add Power Pivot/View Until September 10, 2013

August 27, 2013

Update Oct 11, 2013:  I’ve been given the “all clear” by Microsoft and from readers that as of this week, Excel 2013 Standalone DOES include and successfully install Power Pivot!

See this post for details.


After a few readers of last week’s post reported that installing Standalone did NOT add Power Pivot and Power View, we followed up with my contacts at Microsoft.

Microsoft quickly tracked down a problem in the upgrade logic, and it will be fixed in the September 10th update (that gets automatically downloaded).  So hold tight until then.

Power Pivot, Power View Now Available in 2013!

August 20, 2013


Update Oct 11, 2013:  I’ve been given the “all clear” by Microsoft and from readers that as of this week, Excel 2013 Standalone DOES include and successfully install Power Pivot!

See this post for details.


…and there was much rejoicing

Well folks the wait is over.  Microsoft now offers a way for us to get Power Pivot at home in 2013!

It’s not quite “free” like it used to be (and still is) in 2010, but it’s not super expensive either, and the mere fact that we CAN buy it through retail channels is a big deal.  All in all, I call this a Good Thing.  It is most welcome.

All you need is love.  Oh, and Excel Standalone.

So, if you have Office 2013 already installed and you want to add Power Pivot, you just buy Excel Standalone and install THAT over the top, and you get Power Pivot and Power View.

If you have don’t have 2013 installed and have no desire to get all of the other apps, hey, you can just get Excel Standalone and forgo the rest I guess Winking smile

Power Pivot Inside:  A 2013 Option At Last             Power Pivot Inside:  A 2013 Option At Last

Amazon Purchasing Options:  $99 Download (Left), $79 “Non-Commercial” Version (Right)
Click Images for their respective product pages
HOLD OFF UNTIL SEPTEMBER 10th – see the update at the top of this post

(Note that both of those image links are affiliate links – if you purchase through those, I get a few dollars and you pay nothing extra.  Support the site, that sort of thing.)

“Non-Commercial” Version?

Yeah what the heck IS that?  From a trusted Microsoft source, we have this definition:

Read the rest of this entry »

PowerPivot vs. Power View: What’s the Difference?

June 18, 2013

I’ve been getting this question a lot lately:  How does Power View relate to PowerPivot?  Is PV a replacement for PP?   (And why does PV have a space in it while PP does not?)

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:

PowerPivot's Relationship to Excel


Read the rest of this entry »

A funny thing happened in Redmond…

March 5, 2013


Scene:  A Taco Joint in Redmond

Two weeks ago.  I’m in Redmond for the MVP Summit.  I’m meeting an old friend and colleague for lunch at our old favorite taco joint.  We sit down at our table.  I glance over at the booth right next to us, and who do I see?

The Microsoft executive most directly capable of altering the 2013 PowerPivot story.

When I was still working in Redmond, I might pass this guy in the hallways once every year.  But here I am, in town for a few days, and he’s sitting right next to me at a hole-in-the-wall taco joint two miles from campus.

A fateful, pulse-quickening moment.  What do I do?  (Story continued below).

An Update (of Sorts) on the 2013 PowerPivot Story

The most-commented post in this history of this website continues to churn, and I thought it was time today for me to check back in and tell you what I can.

I will say, up front, that I will not violate my NDA with Microsoft, nor will I betray the good faith of anyone at MS who opens up and talks to me.  Doing either of those would not be in my best interest or that of anyone reading this, since that would crimp any future information flow.

That message bolded above is aimed primarily at anyone from MS who might be reading this and getting nervous about talking to me. 

Got that, agent of Redmond?  You have nothing to fear from me Smile.  And really, I came here today not to bury Caesar, but to praise him.

First, a word on perception vs. reality

Read the rest of this entry »

Guest Post from Ken Puls: How to buy PowerPivot 2013, and the $30 Volume Licensing Workaround

February 26, 2013

An Amusing Solution!

In the long-running comment thread on the Who Moved My PowerPivot Cheese post, one of the recurring themes is “hey, just let me pay a small amount extra for PowerPivot in 2013, but give me a way to buy it ok?”

At the MVP Summit last week, Ken Puls mentioned that he has a way to do precisely that.  You pay about $30 for the right to buy a Volume License copy of Office 2013 Pro Plus.

I haven’t tried this myself but Ken certainly has.  Consider this a viable workaround until further notice.  Take it away Ken…

The Official Purchasing Channels

Rob recently put up a post on the availability of PowerPivot in Office 2013, and how it wouldn’t ship in all Excel SKU’s. This is a huge issue, to be sure, so I thought I’d quickly summarize the software distribution channels so you can see where you will/won’t get PowerPivot if you buy into the 2013 package.

You’ve got two ways to buy a copy of Office 2013 Pro Plus (the version that includes PowerPivot):  Volume Licensing or an Office 365 Business subscription (the Home subscriptions do NOT include PowerPivot). 

Each can be further broken down (see Chris Webb’s blog on Office 365 options here), but to keep this easy to follow, I’ve kept it to key comparable SKU’s:


The $30 Volume Licensing Workaround!

Now, here’s the really funny part about the above though… everything you’ve read so far would give you the impression that getting a volume license is going to be tough and expensive. It’s actually not.

Read the rest of this entry »

“Hey, Who Moved My (PowerPivot 2013) Cheese?”

February 14, 2013


Update Oct 11, 2013:  I’ve been given the “all clear” by Microsoft and from readers that as of this week, Excel 2013 Standalone DOES include and successfully install Power Pivot!

See this post for details.


PowerPivot and Power View Not Available in Excel 2013?

Are These Missing for You in Excel 2013?
The Short Answer is That You Need “Pro Plus” – “Professional” is NOT Enough

Overdue Post in Response to a Popular Question

I’ve been getting tons of questions about this – in email, twitter, on forums, etc.

The questions all go something like this:  “Hey Rob I just bought Office 2013, went to enable PowerPivot, and it’s not there!  It was supposed to be included in 2013 right?”

It’s a fair question of course.  And yes, my original expectation was precisely that – it would be in 2013 for everyone.

Well the short version is that Microsoft took PowerPivot and Power View OUT of most versions of Office 2013.

We will now pause for a moment of disappointed silence Sad smile

If you want to know how to “fix” this problem, skip to the end of the post.  First, I want to tell a story, because the innards of MS are often something that interests people.

“Why Would They Do Such a Thing??”

Read the rest of this entry »

Mini-post #3 of 3: Are You Using Excel 2013 Yet?

January 29, 2013

Quick poll:  are you using 2013 yet?  (Don’t worry if you are not – most of my computers are still running 2010 but I want to know how many of you have already taken the plunge).

Guest Post from Kasper! Excel 2013, Power View, top 10, “long tail” and how DAX helps

January 3, 2013

Welcome to 2013!

Hi folks, welcome back Smile  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.

Read the rest of this entry »