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.

Read the Rest

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:

Percentile Measures Update Figure 1

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.

Read the Rest