Different Conditional formatting rules for each value in x-axis

[delsner]

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!

[v-xicai]

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.

[amitchandak]

@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-num...
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/811692

[delsner]

Thanks @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. 

[AllisonKennedy]

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).

[delsner]

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!

[AllisonKennedy]

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]

That makes sense @AllisonKennedy . The sales% for each store is a measure, which is calculated as SUM(Sales)/SUM(Total Sales). I created the table as you suggested with StoreID and Target. I understand your formula of IF([Sales%]>MAX(Target[Target]), "Green", "Red"). However, that formula when added as conditional formatting only makes Store A red since it's the only one above 5%. I'm assuming that the column chart isn't looking for the MAX of the Target of each respective store, but the MAX of all Targets. What formula would I need so that I get something like this:

I think there's something I'm missing. Thanks again.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[AllisonKennedy · Accepted solution]

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)

[delsner]

Thanks @AllisonKennedy I got it to work. I used your solution. The problem was in my relationship. I needed to set it as Both for Cross filter direction. I was also able to build in the yellow range.