## Conditional Formatting with Different Thresholds per Test, per Product, per Store, per Division, etc.

After a long hiatus, David Hager has returned with a new guest post.  He has a clever Excel trick/formula for applying different conditional formatting “acceptable ranges” depending on the context of the current row.  In his work, different Tests have different acceptable ranges of values that qualify as Pass/Fail/Warning.

Each Test Has a Different “Pass Range” and “Safe Range” –
David Hager’s Technique Translates This Table Quickly into CF Rules

I think this technique can be extended to basically anything:  an acceptable sales growth figure for Store A may be different than that for Store B (or Product A vs. Product B, etc.)

His post also got me thinking about the new “KPI” feature in PowerPivot V2, so I will return later today with a brief follow-on post.

## Percentile Measures in DAX – Errata

Guest Post by Colin Banfield [LinkedIn]

In September of last year, I posted two articles on creating percentile measures in DAX.  See Creating Accurate Percentile Measures in DAX – Part I and Creating Accurate Percentile Measures in DAX – Part II. About three months after I posted Part I, Richard Mintz left a comment indicating that he was having trouble getting correct results when his data sets had a wide range of values and many duplicates. I haven’t been receiving notifications when comments are left, so it’s purely by chance that I saw Richard’s comment recently.

During the process of building the measures, I did do some testing with duplicates, but the testing was minimal and involved only duplicates at the 25th or 50th percentile mark.

To check out the reported issue, I built a new data set with many duplicate values. Figure 1 shows the results of the percentile calculations in this scenario:

Figure 1

Huh? What’s going on here? The 50th and 75th percentile calculations are correct, but the 25th percentile calculation is totally and utterly incorrect! I created several intermediate measures along the way to the final result, so it was trivial to track down and correct the problem.

## Workarounds for “Canceled Due to Memory Pressure”

The Operation Has Been Cancelled Due to Memory Pressure

You can run into this error via a number of different routes, but one way or another, your computer ran out of memory while PowerPivot was trying to do something.

I’ll list some potential fixes here:

## PowerPivot V2 is Released!

Well the wait is over.  It is now released, for real.  No longer a beta or RC.  The real deal. Download it here. Other important links: My first glimpse at new features here. Important warnings about compatibility with V1.

## Downgrading From PowerPivot v2 to v1

OK, let’s say you already installed v2, whether inadvertently or…  advertently. And now you need to downgrade to v1, so that your workbooks function on your server, or can be opened by other people running v1 on their desktops. So……

My former colleagues at Microsoft have responded to the issue I raised last week and have updated the PowerPivot.com download page to include links to both the “beta” of v2 as well as the latest, trusted version of v1. Still…

“Danger, Will Robinson!” I’m seeing this pop up a lot now, both in our hosting business and on forums. If you have gone to PowerPivot.com lately and downloaded PowerPivot, you downloaded a beta (a release candidate) of PowerPivot v2,…

## Six Observations from the 2011 PASS Keynote

The PASS Summit tends to be one of  Microsoft’s favorite venues for unveiling big news in the BI space.  As you may recall, the 2010 Summit revealed some amazing things for the PowerPivot world. Yes, I know that the 2011…

## Mini-Post 3/4: DAX.xml Update for Notepad++ from Colin Banfield

(Rob's note:  Apologies to Colin, he put this up here in draft form weeks ago and even though I promised to flip it to live two weeks back, I forgot.  So Colin...  a thousand pardons.  This is awesome!) From Colin:…

## Creating Accurate Percentile Measures in DAX – Part II

Intro from Rob:  today, Colin finishes in Percentile Measures topic from last week. Note that 1) this technique requires the new release of PowerPivot that is currently still in beta (the Denali release)… …and 2) I would rate this one…