PowerPivotPro is Coming to Houston

April 17 - 19, 2018


**Use the discount code “3ORMORE” when signing up 3 or more people.

APRIL 17 - 18

Foundations: Power Pivot & Power BI

Instructor: Austin Senseman

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work. Two Days in our class and you are EMPOWERED!


  • Not just the “hard” skills, but also the “soft” stuff (when and why to use it, how to get the best results for your organization, etc.)
  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • You don’t need to be an IT professional – most of our students come from an Excel background
PowerPivotPro Training Classes - Houston
PowerPivotPro Training Classes - Houston

APRIL 17 - 18

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

Foundations taught us how to remove repetitive, manual work and make impactful insights. Advanced DAX is about making it rain money by better informing decisions!


  • Taught completely in Power BI Desktop
  • If Foundations is a 101 course, hands-on work experience with DAX is 201, and Advanced DAX is 301.
  • This class will teach you how DAX really works, how to build complex reports that are still digestible, and how to use that information to drive your business.


Level Up Series: Power Query for Excel & Power BI

Instructor: Ryan Bergstrom

Copy-paste? Dragging formulas down? SAME THING EVERY WEEK?… No more. Teach your computer how to build your reports for you. Set and forget!


  • This class will teach you how to connect to all of your data (no matter where it lives), shape it so DAX can run automagically, and have your computer remember the steps so you never have to do it again.
  • You don’t need to be an IT professional – most of our students come from an Excel background
  • Taught simultaneously in Excel and Power BI
PowerPivotPro Training Classes - Houston
PowerPivotPro Logo

Yes folks, we’ve seen all manner of parameterization by slicer.  We’ve even seen sort by slicer.  But now it’s time for me to track down a hunch I’ve had for awhile now:  we can also control conditional formatting via slicers!

At the 65th Percentile, Model Name Profits are Being Shaded Green


Getting Stricter:  Use the Slicer to Set the “Green Threshold” to 95th Percentile

Another Disconnected Table Technique

An old favorite.  First we just create a single column of numbers in Excel – the numbers that we want to appear on our slicer:


Copy a Single Column of Numbers Out of Excel


Paste as New Table in PowerPivot



Resulting CFMinBar Table

Then I write a measure on my new table:


Measure That “Harvests” the Selection from the Slicer –
Could Just Have Easily Used MIN() Rather than MAX()

Which then looks like this in a pivot:


Using Our New Slicer and Measure to Control Conditional Formatting

OK, let’s make a real pivot now – one with a useful measure on it:


Rather than display our [Selected Min CF Bar] on the pivot, I’m going to use a cube formula, off to the side, to capture that measure:


Cube Formula Used to Capture the [Selected Min CF Bar] measure value.  Note how it references the Slicer too, otherwise it would always return 95%.

Adding a Color Scale Rule

I’m just going to add a 3-Color Scale Rule to the pivot:


Adding the 3-Color Scale CF Rule

Then set it to affect the entire measure:


Resulting in:


Simple 3-Color Scale CF Rule

Now for the trick!

Here’s the sneaky part.  Go to Manage Rules:


Edit the one rule we have on the pivot, bringing up this dialog:


Note the Circled Buttons!  Those are Refedit Controls!
The Thresholds in the CF Rule Can Be Cell References!

OK, let’s set that to reference the cube formula cell I created previously:


Set it to Percentile and Reference the Cube Formula Cell

And then…

A Bug!

Or at least, I think it’s a bug.

Click OK and we get:


I really like the “was this information helpful?” link

Yes, I realize that it’s expecting an integer like 90 rather than a percentage value like 0.90 which is what my measure returns.  But I get this error message even when I just type the number 90 into a cell and then reference that cell. 

But it works if I just type 90 directly into the refedit control rather than a cell reference, it works.  Something is broken here.

I Still Got it Working by Using “Number”

I think it’s only the Percentile option that doesn’t like cell references.  The Number option works:


The Number Option DOES Work with Cell References

In order to use the Number option to let the user control percentile-based conditional formatting with a measure like [Profit], however, I need to get a bit more sophisticated with my measures.

I need to calculate, for instance, what the 90th-percentile [Profit] value is.

And that’s what I did.  But I’m out of time for today’s post.  So the full explanation must wait until Tuesday, sadly.

But in the meantime, you can experiment with the Number option as illustrated above, and have it reference a cube formula cell that returns a measure.  By Tuesday some of you may have more sophisticated examples than I do Smile

***UPDATE:  The second installment of this technique is now posted here.

  Subscribe to PowerPivotPro!


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 2 Comments

  1. Yes! Use this technique to dynamically set upper and lower limits for conditional formatting choices that are not color fields. Build two measures instead of one, and you have complete control over stoplight icons from a slicer or two. Next step – combine these measures with other variables in slicers to build visual what-if views (If I increased distribution to X% without exceeding 100%, which products would contribute the most incremental units? Now show them to me with highlighting!).

    PowerPivot is the ultimate playground, and Rob just keeps dumping toys on the field for all to use. Nice work!

  2. What about making diferrent conditional formating depending on what slicer combination you choose?
    For example if you have different targets for each fiscal year such as:
    -In 2005, at the 65th Percentile, model name profits are being shaded Green
    -In 2004, at the 60th pecentile, model name profits have to be shaded green

    Is it posible to do that?

Leave a Comment or Question