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.

@brixmitch,

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

Thanks.

  • 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//
SWITCH (
    TRUE (),
    [Policy # & Limits] = 0, "#ffffff",
    [Policy # & Limits] = 1, "#cc2222",
    "#ffffff"
)

Thanks
Enterprise%20DNA%20Expert%20-%20Small

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:

Conditional%20Formatting%20Summary%20Value%20Example

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!

@brixmitch,

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 %.
SWITCH (
TRUE (),
[%]=1, “#ffffff”,
[%] <1|| [%] >1, “#cc2222”,
#ffffff
)

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:
image

Thanks
Enterprise%20DNA%20Expert%20-%20Small

@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 = 
SWITCH( TRUE(),
    [% Allocated] = 1, 1,
    [% Allocated] < 1, 0,
    2
)

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.

Cheers
Enterprise%20DNA%20Expert%20-%20Small

@brixmitch,

:+1:.
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

image