Conditional Formatting for not-equal to a summary value

[brixmitch]

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.

[BrianJ]

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

Aggregate hours per week per employee then show weeks where total < 40

@ferdsjoseph, Here’s one way to do this using three branched measures: Total Hours = SUM( 'Hours Worked'[Hours Worked] ) Total Hrs Employee by Week = CALCULATE( [Total Hours], VALUES( Employees[Name] ), VALUES( Dates[Week Number] ) ) Total Hrs Employee by Week < 40 = IF( [Total Hrs Employee by Week] < 40, [Total Hrs Employee by Week], BLANK() ) [image] Full solution file posted below. I hope this is helpful. Brian eDNA Forum - Aggregate Hours Less Than 40.pbix (93.6 K…

[JarrettM]

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

[brixmitch]

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!

[BrianJ]

@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

[JarrettM]

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:

Thanks

[BrianJ]

@JarrettM, @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
)

• Brian
  eDNA Forum - Labor Not 100 Percent.pbix (89.4 KB)

[brixmitch]

Awesome, thanks!!!

[brixmitch]

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!

[JarrettM]

You even got 2 different approaches.

Cheers

[BrianJ]

@brixmitch,

.
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