Forum Discussion
Conditional format in matrix table can't exceed 100% (when using measures)
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?
13 Replies
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-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-valuesThanks 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.
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
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?
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
