Forum Discussion
Different Conditional formatting rules for each value in x-axis
I have a column chart with region on the X-axis (A,B,C,D,E). Each region has a different percent requirement (i.e. A=5%, B=4%, etc). Is there a way to have diverging conditional formatting that is set differently for each value in the X-axis, in case for each region. Basically, I want to show green if below and red if above a different number for each column. Also, would this type of conditional fomatting be possible in a line chart that shows each region in the legend and date in the X-axis?
Thanks in advance!
- Have you created a relationship between the Target table and the Store or Sales table? If you do this, and the column chart has StoreID on the Axis, then the MAX() part of the formula should find the MAX target% for the selected storeID in the column visual. If you're trying to do this for more than one store at a time, you'll need to create a more complex measure and explain further what you need.
You should be able to use SELECTEDVALUE(Target[Target]) instead of MAX if that makes more sense.
If you want to you could build out the DAX further to add some error checking:
IF(HASONEVALUE(StoreID), Measure)
10 Replies
- You would need to create a measure that calculates the difference between the value in the X-axis and the target value, then apply conditional formatting based on the value of that measure (if below zero then red, if above zero then green).
Thanks AllisonKennedy Could you kindly give my an example of what a measure like that would look like? I've included a picture of what I'm trying to do. There are 6 different stores and each store has a different target amount. Thanks!
You'll need to provide a bit more info about your data model, column and table names, relationships, etc if you want detailed help from the community.
I would also recommend storing the Target values as a table in Power BI, something like:
StoreID Target
A 5% B 1% C .75% D 3% E 1% F 1.5% Then use that Target table in a measure, similar to:
IF([Sales%]>MAX(Target[Target]), "Green", "Red")
Note this doesn't account for between, in order to do that you will need to define what you mean by 'between', either as a percentage of the target, or as another column in the target table.Hope that makes sense.
delsner , You should able swap conditions.
Or create a color measure like give below with you need and used that in conditional formatting after choosing "Field" option.
Color Date = if(FIRSTNONBLANK('Date'[Date],TODAY()) <today(),"lightgreen","red") Color Date = var _min =minx(allselected(Date,Date[Year]) return Switch( true(), FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"lightgreen", FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"blue", "red") if(FIRSTNONBLANK(Table[Value],"true")= "true","green","red")Color Field - Color Measure - Conditional formatting - refer for steps
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-values
https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/
https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692Thanks amitchandak . Maybe I wasn't clear enough in my explaination. I'm going to make it simplier now. The column chart I have is below. The values for the columns are % sales from old customers. The numbers in red are the targets for each store. I want the column for each store to be either green, yellow, or red depending on if each store is below, between, or above its' value.
Hi delsner ,
You create measure like DAX below, choose Background color under Conditional Formatting for your goal field (In your scenario, it is [Region]. ), format by "Field value" based on [Conditional Measure] . See more: Use conditional formatting in tables.
Conditional Measure = VAR _Ave= AVERAGE(Table1[Value]) RETURN IF(MAX(Table1[Value])>= _Ave, "Red", "Green")Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
