Each Number Refedit in the Edit Rule Dialog Points to a Different Cell on the Sheet

Correcting for that bug

In the last post on this topic, I discovered what I think is a bug in Excel 2010 conditional formatting, one that prevented me from using the Percentile threshold type with a cell reference.

But the Number type works great, so if I can get the actual [Profit] value for, say, the 80th percentile Model Name into a cell, I can reference that.

And that’s precisely what those three cells in the image above contain:

Cell F2 Contains a Cube Formula that Returns the Measure [Profit Required for Green CF]

OK, so how do I calculate that [Profit Required for Green CF] measure?

The [Profit Required] Measures

Here’s the formula for the [Profit Required for Green CF] measure – the one displayed in cell F2 above:

[Profit Required for Green CF]=

MINX(TOPN([Model Name Rank for Green CF],
ALL(Products[ModelName]),
[Profit]            ),
[Profit]       )

The TOPN() function returns a set of rows from ALL(Products[ModelName]), ranked by the [Profit] measure.  But how many rows does it return?  The [Model Name Rank for Green CF] measure determines that:

[Model Name Rank for Green CF] =

CEILING(
(1-[Selected Min CF Bar])*
[Number of Model Names with Profit],
1
)

Recall from the previous post that [Selected Min CF Bar] returns a value between 0 and 1, such as 0.90 if the user selects 90th percentile on the slicer.

OK, so what’s that other measure?

[Number of Model Names with Profit] =

CALCULATE(COUNTROWS(VALUES(Products[ModelName])),
ALL(Products[ModelName]),
FILTER(ALL(Products[ModelName]),
NOT(ISBLANK([Profit]))
)
)

The purpose of this measure is essentially to tell me how many products have actually sold.  It weeds out products that are in my catalog but have not sold yet, or not recently enough, etc. – depending on slicer selections.

So if I have 100 products that have actually sold, and the user has selected 90th percentile on the slicer, [Model Name Rank for Green CF] becomes:

CEILING(
(1-0.9)* 100,
1
)

which is 10.

And then [Profit Required for Green CF] becomes:

MINX(TOPN([10, ALL(Products[ModelName]),[Profit]),
[Profit]    )

Which grabs the [Profit] value of the lowest-ranked product in the top 10!

That is the value that gets returned into cell F2, which is what the CF rule references!  Any product with a [Profit] value greater than or equal to the [Profit] value of the 10th-most-profitable product will be colored green!

The Yellow and Red Thresholds

I’ve just explained how I set the Green threshold.  There are multiple ways to handle the Yellow and Red.  You could give the user three separate slicers and let them control each independently.  Seems like overkill in this case, so I just hard-wired those to be 20 and 40 percentile lower than the Green threshold, respectively:

[Model Name Rank for Yellow CF]=

CEILING((1-[Selected Min CF Bar]+.2)*[Number of Model Names with Profit],1)

[Model Name Rank for Red CF]=

CEILING((1-[Selected Min CF Bar]+.4)*[Number of Model Names with Profit],1)

And then the rest of the measures are the same for both Red and Yellow as they were for Green.

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.