Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
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.
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.
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,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Before I attempt your second suggestion, does your first suggestion allow the use of color scale formatting or only rules-based color coding?
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
Thank you. Unfortunately neither of these solutions work for me, as I need the color scaling to be applied to Outreach only (in this example), and not the entire table as a whole. Even if I unpivot the columns, my measure will still be filtering on the Outreach attribute, which will still result in all zeros (occasionally, depending on the date range).
It's the behavior of the color scaling in the conditional formatting that's the issue, which is set to use the maximum color value instead of the minimum color value when all values are the same, in a column.
Hi @Anonymous ,
Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.
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,
Winniz
This does not solve my problem because the maximum values are not known. I would still like the largest values to be dark-colored, and the smallest values (zeros) to be light-colored.
If all values become zero, the color displayed is darkest, not lightest, which is not desirable.
Hi @Anonymous ,
Try to customize the minimum and maximum values, where the minimum value is 0.
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,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This does not solve my problem because the maximum values are not known. I would still like the largest values to be dark-colored, and the smallest values (zeros) to be light-colored.
If all values become zero, the color displayed is darkest, not lightest, which is not desirable.
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.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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 Curtis,
Are you able to share a screen shot of your setup and some data?
Unless I'm misunderstanding something, I'm not seeing that behaviour.
Regards,
Kim
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi @Anonymous
Change this feild to don't format instead of "As Zero"
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
This only works for null or blank values. As I mentioned in my question, it does not apply if my values are zeros.
Just out of curiosity did you try using Custom like here
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Yes that was the last thing I tried before posting here, which didn't work unfortunately.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
71 | |
57 | |
38 | |
36 |
User | Count |
---|---|
81 | |
67 | |
61 | |
46 | |
45 |