Conditional format in matrix table can't exceed 100% (when using measures)

[Namoh]

Hi,

I've got a matrix table with 4 columns.

I would like to color two of these columns (based on the other two columns) via conditional formatting.

The colors should be shown via the following rules:

- Dark Green when the column value is <80% of the reference column

- Light Green when the column value is >80% and <110% of the reference column

- Orange when the column value is >110% and <120% of the reference column

- Red when the column value is >120% of the reference column

When trying to do so the OK button will be greyed out once I put anything higher then 100%....??

See picture 1, with OK possible when entering 100%

See picture 2, with OK greyed out when entering 101%

It's not something special I'm looking for, in excel I can do this within 5 seconds.

How to fix this in PowerBi?

[v-lili6-msft]

hi @Namoh 

you must know that the way the "Percent" conditional formatting works is different than you think, it doesn't have anything to do with percent of total.  It take the lowest and hightest displayed amounts and uses those to determine the formatting bands based on the Percent you entered so your table is looking at these numbers to apply the formatting.

So for your case, just replace "Percent" with "Number" 

Regards,

Lin

[Namoh]

Hi v-lili6-msft, thanks for your reply.

I tried your suggestion to use Number instead of Percentage and although I get different colors, they're not as they should be.

First picture is what I've entered, second picture is what it shows.

As you can see, the colors are not according to what I want. Picture 3 shows the current colors (2nd column) and what they should be (4th column), for the first 10 rows op picture 2.

I can't figure out what the logic is that's used to determine the colors, so I could adjust accordingly to get what I want.

PowerBi must be able to provide a simple Conditional Formatting like excel, right...?? 

If my Actuals are <80% of my Targets, the Actuals should be Dark Green.

If my Actuals are >80% and <110% of my Targets, the Actuals should be Light Green

If my Actuals are >110% and <120% of my Targets, the Actuals should be Orange

If my Actuals are >120% of my Targets, the Actuals should be Red.

How to make this happen in PowerBi?

[v-lili6-msft]

hi  @Namoh 

You should create a measure as below:

Measure = DIVIDE(SUM(actual unplanned downtime(hrs)) , SUM(target unplanned downtime(hrs)))

then do conditional format based on this measure not target unplanned downtime(hrs).

and since this meausre is a percent, you need to change 1->0.01 80->0.8  110->1.1 120->1.2  9999->99

Now it should work for your requirement.

Regards,

Lin

[Namoh]

Thanks, will try to use the extra measure!

Will post if it worked.

[Namoh]

@v-lili6-msft , I've created the measure and it shows the correct values, see 2nd picture below, last column.

I've added the conditional formatting rules accordingly, but the colors are not showing up correctly, see picture below.

What am I doing wrong?

According to above rules the first 3 rows should be Dark Green, because they're less than 80% / 0.8, but they show as Red....?

[v-lili6-msft · Accepted solution]

hi @Namoh 

here still is Number instead of Percent

Regards,

Lin

[Namoh]

Yes, that solved it.

To be honest the Conditional Formatting this works in PowerBi is much less intuitive then how it works in Excel.

The fact that you need to create an extra measure is a step I really don't understand, with my knowledge from Excel.

But then again, I'm a newbie when it comes to PowerBi, but it just isn't as easy and intuitive as Excel.

[v-lili6-msft]

hi  @Namoh 

I'm sorry i don't know this language in the picture, could you please share your sample pbix for us  have a test, that will be a great help.

Regards,

Lin

[Namoh]

Hi v-lili6-msft, I found the option to change to English.

Hereby the screenshot in English, hope this helps.

Unfortunately my pbix contains lots of classified info, so I can't share it, but I hope above information is enough.

If more is needed, just let me know.

Don't know if it matters, but the columns in my table are measures.

[v-lili6-msft]

hi  @Namoh 

From the picture, this result is right for your setting.

you are doing conditional format for actual unplanned downtime(hrs) based on target unplanned downtime(hrs).

so it is right result. 

what is your expected output?

Regards,

Lin

[Namoh]

Hi @v-lili6-msft , thanks I now realise that it looks at the value of my Target values.

But this is not what I want.

I want the following:

If my Actuals are <80% of my Targets, the Actuals should be Dark Green.

If my Actuals are >80% and <110% of my Targets, the Actuals should be Light Green

If my Actuals are >110% and <120% of my Targets, the Actuals should be Orange

If my Actuals are >120% of my Targets, the Actuals should be Red.

So first row Target is 95 Actual is 62, Actual is <80% of Target so this should be Dark Green.

I expect colors like the 4th column (D) in below picture according to above 4 rules.

Column C shows the percentage of Actual compared to the Target, column D shows the color that I would like to be returned.

[amitchandak]

@Namoh ,

Actually if % type of measure that you should use absolute value. or create a % measure and use absolute value.

You can log an issue or Idea if it not working.

https://community.powerbi.com/t5/Issues/idb-p/Issues

https://ideas.powerbi.com/ideas/

You can create a color measure and use with field option

example

color =
switch ( true(),
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity1" && sum('Table'[Value]) >500,"lightgreen",
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity2" && sum('Table'[Value]) >1000,"lightgreen",
// Add more conditions
"red"
)
calculate( [NumberofID],DATESMTD('Date dimension 'Table''[Date]), USERELATIONSHIP ('Table'2[CreateDate])
calculate(Max(Target[ratio])) *CALCULATE(
[Sales_Value],
SAMEPERIODLASTYEAR(
Date_DB[Date]))

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[Namoh]

Thanks for your quick reply, but to be honest, I don't understand what you mean.

This is my table, with normal values and the YTD columns are sums of the 1ste/3rd column.

If more information is needed, e.g. my measures, to provide a solution that I can work with, please let me know.

I looked at the links besides that they are very interesting, I didn't find a solutoin for my issue.