Matrix Conditional Formatting when all values are zero

[Anonymous]

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

[Inigo_Montoya]

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.

[tbutlerastor]

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.

[v-kkf-msft]

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.

[Anonymous]

Before I attempt your second suggestion, does your first suggestion allow the use of color scale formatting or only rules-based color coding?

[v-kkf-msft]

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

[Anonymous]

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.

[v-kkf-msft]

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

[Anonymous]

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.

[v-kkf-msft]

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.

[Anonymous]

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.

[aj1973]

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.

[Anonymous]

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!

[KNP]

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.

PBIX File

Regards,

Kim

[Anonymous]

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.

[KNP]

The screen shot explains perfectly. Thanks for that.

I'll have a think about it.

[aj1973]

Hi @Anonymous 

Change this feild to don't format instead of "As Zero"

[Anonymous]

This only works for null or blank values. As I mentioned in my question, it does not apply if my values are zeros.

[aj1973]

Just out of curiosity did you try using Custom like here

[Anonymous]

Yes that was the last thing I tried before posting here, which didn't work unfortunately.

[amitchandak]

@Anonymous , I think that is expected behavior, if all values are the same then it is treated as max