Forum Discussion
Matrix Conditional Formatting when all values are zero
Hello,
I'm using conditional formatting in a matrix to set the background color based on color scale. However, when all the values in the column are zero (not null or blank), the color defaults to using the maximum color instead of the minimum color.
Is there a way to change this behavior to use the minimum color instead? Rather than setting the values to null, as I need to keep them as zeros to include them in averages.
EDIT: Image for clarity
Thank you,
Curtis
24 Replies
I'm having this exact issue. None of the replies to this question seem to understand the problem. It's a bad default behavior and should be changed.
Anonymous , I think that is expected behavior, if all values are the same then it is treated as max
Hi Anonymous
Change this feild to don't format instead of "As Zero"
Just out of curiosity did you try using Custom like here
As specified in my question, it's not when some values are zero, but when ALL values in a column are zero, it defaults to maximum color scale, instead of minimum. I'm wondering if it's possible to change this to use the minimum color instead. The screenshots you posted show values other than zeros. See below for a sample. "Outreach" showing as green when I would prefer it to be white.
The screen shot explains perfectly. Thanks for that.
I'll have a think about it.
As I told you, you need a DAX measure to control the outcome of that column "Oureach".
If you can share a Pibx Sample, we can help you with it.
If you simply create a PBIX and "Enter Data" with just 0 for the values, "A,B,C" for categories, and apply conditional formatting color scale in a matrix, you will see what I mean. I'm not sure how to attach files in this comment without hosting a link to it somewhere. Please see my screenshot as well for my results.
If you intend to create a DAX measure to assign different arbitrary values close to zero, instead of maintaining the actual value of zero, I'm not interested in this type of solution as it will also affect sorting functionality.
Thank you!
Hi Anonymous ,
You can create the following rule for Outreach column:
Or you can also unpivot other colums, then the matrix will recognize zero as a minimum.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
I used color scale formatting for the referral column and only rules-based color coding for the Outreach column in my first suggestion.
Best Regards,
Winniz
For you and anyone else finding this, these are the settings that I was able to configure that fixed this issue for me today. Add a middle color with a custom value of 1. Move the previous lowest color of the gradient to the middle color and set the Lowest Value to #FFFFFF or whatever you want that 0 to show. Empty values setting is irrelevant, but I set it regardless.
If this was helpful, let me know. If it doesn't work, let me know that too. Seems like a mixed bag.
