PowerPivot KPI's in V2:  What Are They and How Do We Use Them?

A Shiny New Dialog to Play With!

Better Late Than Never!

On Tuesday, in my intro to David Hager’s post, I promised to circle back “later today” and add some follow-on thoughts.  Well, ONE of those words ended up being truthful Smile

But I’m back today!  Let’s not dwell on the past shall we?

In David’s post, he ended up with a “TestStatus” column set to Pass/Fail/Warning depending on a) what the test value reported  and b) the specific acceptable ranges established for that particular kind of test.

He was then able to use that as a slicer.  But what about conditional formatting?  His original example, a regular (non-pivot) table, DID include conditional formatting.

This is where I thought it would be good to finally check out the new KPI feature in PowerPivot V2.

What is a KPI?

It stands for Key Performance Indicator.  Essentially though, what it means in practice is “a measure that has conditional formatting built into it.”

Pretty cool concept really – imagine being able to define your conditional formatting rule once and then use it any pivot with a single click!


Amir and Me:  my own twisted history with this feature

Back around 2005/2006, when I was working on the Excel team at MS, I essentially conspired with Amir Netz to “sneak” this feature into Excel.  (What this meant, specifically, was that Excel learned to recognize when a measure carried built-in conditional formatting, and then translated that into a conditional formatting rule in the pivot).  At the time, that feature only benefited organizations that used the traditional Analysis Services server product, and had zero impact on the average Excel Pro.

Ironically, a few years later when I was working on PowerPivot V1 (still at MS), I then opposed Amir’s desire to add KPI’s to PowerPivot.  My reasoning was basically “I can’t use a KPI to apply data bars or color scales, which are by far the two most useful types of conditional formatting, so let’s not burn valuable development time on a feature of limited value to Excel pros.”

Partly because of my resistance, KPI’s didn’t make it into V1.  But here we are in V2, I’m not at MS anymore, and we have the feature.  So let’s see what we can do with it!

Convert From Pass/Fail/Warning to –1/0/1

Conditional formatting doesn’t work well against text values, and neither do KPI’s.  So first I added a new calc column to David’s workbook:


Why –1/0/1?

Because that’s what KPI’s expect you to use.  It’s weird that apparently 0= Good (Green) and 1 = Warning (Yellow), weird enough that I had to find that out using trial and error, but no big deal once you are aware of it.  I also may be missing some underlying principle here – it’s been a long time since 2005/2006.

Create a –1/0/1 Measure

Next, you need a measure that returns the same –1/0/1 values.  So I created:

[Test Status Value]=MIN(TestResultTable[TestStatusNumeric])

Why MIN and not AVERAGE?  Because the measure really does need to return one of those three values and nothing in between (I think).  Averaging them may have unpredictable results.  Let me know if you have tried this and found something different.

Creating the KPI

OK, now I have two measures in my field list:  the original test result value and this new –1/0/1 measure:


I Now Have Two Measures.  Ready for KPI!

OK, so then you right click the original measure and choose create KPI:

Right Click Original Measure (NOT the –1/0/1 Measure) to Create KPI in PowerPivot

Right Click Original Measure (NOT the –1/0/1 Measure) to Create KPI

The Dialog

This is what you see next.  I scratched my head a bit before I figured it out.  Actually I still don’t have it completely figured out but I haven’t circled back to spend time on it yet.


Change the measure dropdown to be your –1/0/1 measure:


Pick the conditional format type that you want (note that it’s ONLY the icon types):


I Haven’t Had Success With These Parts of the Dialog Yet

Ignore this part of the dialog for now – this is the part that I need to research further, I assume it’s something you’d use if you had a measure that returned more than –1/0/1.  If you’ve got this figured out please let me know, otherwise I’ll get around to it.


I Don’t Know What to Do With This Part Yet Smile

I also didn’t see much point in moving the little threshold sliders:


I Left These Sliders at 40 and 80

It may be that those two parts of the dialog make more sense when you choose “Absolute Value” at the top of the dialog as opposed to “Measure.”

The Results

You now get a new KPI “node” in your field list:


See how Value and Status are both checked?  Well here’s what you see in the pivot:


Rob blames Rob!

My first response was, “WHAT???  The conditional formatting gets applied to the –1/0/1 measure and NOT the original value measure?  What the heck kind of decision was THAT?  This eats way more space, an entire extra column in the pivot!”

And then I realized…  if only I had caught that back in 2005/2006, because that is the way the feature got implemented in Excel.  Seriously folks, it’s never all that satisfying to get all morally outraged and then in the next instant realize that it’s your own dumb mistake Smile

It’s also a statement on “don’t rush features into the product at the last minute, because you don’t have time to properly consider how they should be used.” 

And even more than that, my past sin is a reflection of how little I had personally used conditional formatting at that time in my career.  But remember, the fancy new conditional formatting features didn’t even exist until Excel 2007, which was the release we were working on.  I only became a conditional formatting “fiend” once it became as useful and accessible as what 2007 made it.

Anyway, more history and reflection than perhaps you are looking for, but I find it personally fascinating, the evolution of my relationship with all of this stuff.

Final Analysis

Honestly I don’t expect to use the feature that much.  The lack of color scale and data bar is just as critical to me today as it was in 2008 when I was arguing with Amir.  And the (re-) discovery that it requires an additional measure column in the pivot makes it even more awkward for me.

On net my opinion remains the same as it was a few years ago:  this is a feature that was added because the traditional BI crowd expects to see it.  And it demos well to a BI audience because BI audiences tend to think of the field list itself as the way people consume BI (which in my experience is short-sighted – the masses only want to consume finished reports). 

But Excel pros can get much better conditional formatting results if we skip it and apply conditional formatting the “old fashioned way.”  So that, I think, is what we will keep doing.

A shame really – I hope the PowerPivot and Excel teams revisit this KPI feature and correct the shortcomings.  Including the mistakes of one Rob Collie Smile

If you think I have missed something here please let me know, happy to reconsider and learn.

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 5 Comments

  1. Image36.png looks like: Increasing is better (revenue), decreasing is better (product failure), closer to target is better (budget), further from target is better.

    Image37.png: I’m guessing that values are normalized when using a measure and using the sliders with a normalized score verusus a raw score can be a little confusing. There’s a lot of info about this as it relates to PerformancePoint Services.

  2. I have a connect item posted in regards to the new KPI feature requesting some enhancements like a collapsed view. I thought I had requested the conditional formatting options like we have in multidimensional SSAS as well, but not seeing that now. https://connect.microsoft.com/SQLServer/feedback/details/649373/powerpivot-bism-kpi-enhancements-ctp2-denali

    I wish they would remove the first default circle option as well for the icons, not a good choice for people that are color blind, should default to an option that also includes a shape such as the third, fifth, or sixth option.

  3. Those are good suggestions. I also made the point on Connect that showing three icons are redundant and distracting – you can’t draw the user’s attention to anything.

    At any rate, this feature will always have marginal usefulness, IMO. In most cases, one or more charts displaying all this information is infinitely better.

  4. Hi There,

    Regarding the bit of the dialogue that you haven’t worked out yet, that lets you define what is considered good and bad.
    Top left = target is good lower values are bad
    Bottom left = target is bad lower values are good
    Top right = Centre target value is good values either side are bad
    Bottom Right = Centre target is bad values either side are good.

    Also why did you create a measure with values of 1/0/-1? that is totally unnecessary, just define the KPI on the measure that you want to monitor and then define your target either as another measure or a set value and where the boundaries fall (depending on what icon you use).

    Fully agree with Dan’s comment on the colour blind circles, but MS has a very long way to go before it actually understands information visualisation (but not as far as Business Objects!)

  5. Hi..

    i got error on filter using Slicer,

    The operation has been cancelled because there is not enough memory available for the application.It using a 32-bit version of the product consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

Leave a Comment or Question