Conditional Formatting for not-equal to a summary value

I have a table report summing up project percentage allocations for individuals.

For instance, I could be working one project 10% of my time, another 70%, and a third 20%. My table would show me and the summary of my allocations - 100% in this case.

What I want to do is to highlight people with a summary value of anything other than 100%. If they’re 80% they get highlighted for under-allocation. If they’re 110% they get highlighted too.

I’ve been looking into the conditional formatting options and I can’t seem to find a way to make this work.

Thanks in advance for your help.


If you can post a sample PBIX file, I’d be glad to take a look and work through this with you.


  • Brian

Note: you also may want to take a look at this recent thread, which posed a similar issue and might be helpful to you:

I have done this several different ways, but my favorite is using a SWITCH statement and adding the Hex color into it. Here is a sample measure. Then all you have to do is go into conditional formatting and choose by rule.

   Policy Back = //This is background color for Policy//
    TRUE (),
    [Policy # & Limits] = 0, "#ffffff",
    [Policy # & Limits] = 1, "#cc2222",


Conditional Formatting Summary Value Example.pbix (42.7 KB)

Here’s a sample of what I’m trying to do. in the visualization User one’s values would be red - or some other unique formatting - for January - Mar because his/her summary allocations aren’t 100%. User two would be red for Feb - May. And user 3 - 100% for all months - would be untouched.

Critically, these are summary values of detail data in the table - which includes individual project allocations. For the purposes of the visualization i don’t care about the detail, only that an individual allocates 100% of his/her time during the month - no more, no less.

Here’s a snapshot of the visual:


Lastly, i think the central point to all this is that what i want to test for conditional formatting is simply “summary percentage <> 100%”, and that i can’t seem to find a way to reflect that in the conditional formatting rules. They follow the construct of “is greater than X AND less than Y”, when what I need is just a “<>”.

I’ll take a look at the suggestions noted in the other replies to see if any of them might work.

And thanks again!


Thanks for the clarification. I’m on my way home now, but will have a proposed solution back to you later this evening.

  • Brian

Try this Switch Statement for your measure that you get your %.
TRUE (),
[%]=1, “#ffffff”,
[%] <1|| [%] >1, “#cc2222”,

Make sure you choose field value from conditional formatting options, then selected your measure

The #ffffff is a white background and the #cc2222 is a red background, or if you just want the text to be highlighted you would have to replace the #ffffff with #000000, and #cc2222 with #ffffff.

Here is what table would like with measures applied to background and text of table:


@ojones, @brixmitch,

Lots of different ways to handle this one. Similar approach using measures and Rules conditional formatting:

Fully Allocated Single Color = 
    IF( [% Allocated] = 1, 1, 0 )

And if you care about the distinction between < 100% and >100%:

Fully Allocated Dual Color = 
    [% Allocated] = 1, 1,
    [% Allocated] < 1, 0,

1 Like

Awesome, thanks!!!

Ultimately, the key learning for me is that you cannot do conditional formatting in a visualization of a field that is the summary of detail rows. It seems you need to create a measure to summarize the detail, and then a create a 1/0 Boolean-esque expression to base the conditional formatting rule upon.
In my case it was simply "% Allocated = calculate(sum(table[field])) and “Fully Allocated Single Color = if(and([%Allocated]>.999,[%Allocated]<101),1,0)”
My data had some odd fractional remainders off in the 15th decimal position that necessitated the .999/101 logic.
Bottom line - it now works, and thanks!

You even got 2 different approaches.



FYI - if you ever run into that fractional remainder problem again, a simple fix is to wrap the relevant portion of your measure in a ROUND() function.

  • Brian