Measures for conditional formatting are not available to select

[alicia_mtz]

Hi!

I want to colorized my column depending on the value of the row.

- If the value is below 90% in red, between 90 and 100% in black and over 100% in green.

I checked this topic and made a measure for it, modifying the one given as solution: How to apply conditional formatting 

My measure:

Conditional_color = 
VAR Percent_ = 
    [Total] / [Target]
RETURN
    SWITCH (
        TRUE (),
        Percent_ < .9, 1,
        Percent_ >= .9
            && Percent_ < 1, 2,
        Percent_ >= 1, 3)

But when I choose "Field value" to base it on my measure, this measure and all the other measures appear in grey and I cannot select them.

I saw this topic and there is an answer saying to change the data type into TEXT, but I don't have text among my options, only general or numerical options. There is no solution answer on that topic though: Conditional formatting measures not available 

Does anybody know why I'm unable to pick measures to colorised the matrix? Do I have an error on my code?

Thank you so much 😊

[v-shex-msft]

Hi @alicia_mtz,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[alicia_mtz]

Hi @v-shex-msft 

I attach the pbix with some dummy data and the measure I'm trying to use. I also add here a screenshot to see if this helps too. Since I cannot attach it here directly I've uploaded it into my drive. Here is the link to download it:

https://drive.google.com/file/d/1NX-L8syJodQRh2fdrhAqqUIjdQ35qR8K/view?usp=sharing

You see here colourised the value over 100% in green. What I want is the values from 0 to 90% in red, between 90 and 100% in blue (or even better not colourise these ones) and over 100% in green. 

I only manage to colourise the green one and I know I didn't set it up very well either... (you can see it in the pbix)

Thank you so much for your help! 🙂 

[v-shex-msft · Accepted solution]

HI @alicia_mtz,

Here is the measure formula for color formatting, you can create a new measure with these codes and set 'background color' conditional formatting with 'field value' mode on the '% Sales vs Target' field:

color format =
IF (
    [% Sales vs Target] < 0.9,
    "Red",
    IF ( [% Sales vs Target] <= 1, "Blue", "Green" )
)

Notice: If these default color names are not suitable for your requirement, you can also use a 6-bit hex color code to replace the color names in the above formula.

Regards,

Xiaoxin Sheng

[alicia_mtz]

Hi @v-shex-msft 

That's BRILLIANT! That's exactly what I needed, thank you so much! 

I guess I wasn't able to choose my measure because it has an error somewhere in the code... I'm still learning and I should try to keep it simple as much as possible...

I already tried different colours using your code and even hex code and all works.

Thank you very much!! 😊😊😊

[alicia_mtz]

Hi @AnamPatel

Thank you for your answer, unfortunately, that option doesn't work for me.

If you have the last rule as = 100, it doesn't give you the option to colourise also the results above 100, and if you write it as >= 100 (like the 90 value in the previous line) I need to give a maximum value on the right side (previous line as a <100 )  but I don't want to write a number there, because you never know how above target your value can be...

That's the reason I was writing the code, but I cannot select it 😞 

I appreciate you took the time to reply to me, thank you 😊

I hope someone know the reason I'm unable to choose the measure. 🤔

[AnamPatel]

There is an easier way to do Conditional Formatting. You can achieve this without a measure as well. Below are the steps –

Click on the Down Arrow in front of the Column Name under Values Tab and Select Conditional Formatting > Background Color

A new Background Color window will pop up – fill all the details, select the colors you want and click on OK.

That’s it, the colors will show up on your chart –

Let me know this works for you.